DATEDIFF, YEARFRAC - DAX Guide

[@Milhouse77BS]

I like how the results of each line returned in a table. I might use to compare different measures when refactoring.

[@uade129989]

How can i manage business day Alberto? thanks for your videos. Gracias!

[@Bharath_PBI]

Thank you 👍

[@rusi.rusev01]

Very nice! How can you make datediff in business days or excluding holidays? I guess including calendar table somehow?

[@SQLBI]

See these articles and samples: www.sqlbi.com/articles/counting-working-days-in-dax/ www.sqlbi.com/blog/alberto/2011/01/19/working-days-computation-in-powerpivot/ www.sqlbi.com/tools/dax-date-template/

[@matteoagliocchi3268]

Any way to calculate working days between the two dates?

[@SQLBI]

See this article+video: www.sqlbi.com/articles/counting-working-days-in-dax/

[@beginho2454]

Hello Alberto, Microsoft announced the GPT-3 can help us write the measures easily? did you use it?

[@SQLBI]

It's a feature that Microsoft announced but not released, we cannot comment until we try it.

[@beginho2454]

@@SQLBI let me follow the DAX Guru's tips. thanks your team. ;)

[@bladoblado]

Hi, Love your Chanel. I struggle everytime i need to calculate the time elapsed from two different date+ times (Start and end) for our service orders or Sales . Usually i do on powerquery and use duration types, with a lot of programming, but i feel i dont do it right and extremely over complicated. And tips? Dax or M?

[@SQLBI]

In reality you are doing well because the same calculation in DAX is more expensive. DAX works well on aggregations or in row-level calculations - computing the difference in dates between different rows related to the same order and repeating the same process for every order is computationally expensive and it's better to do that preparing data in advance.

[@emanueleruggiero8011]

Ciao Alberto, and congrats for the channel. I stumbled across the famous challenge of calculating the age of customers starting from their birthday. I came up with the below DAX code. So far I could not find any shortcomings with this approach. Do you know if use cases exist when this code won't return the right value? customer_age = SWITCH( -- TRUE, // Set conditions for relationship between current date and customer's birthday // Case when this year's birthday did not happen yet MONTH(TODAY()) < MONTH('Customer Lookup'[birthdate]) || MONTH(TODAY()) = MONTH('Customer Lookup'[birthdate]) && DAY(TODAY()) < DAY('Customer Lookup'[birthdate]), YEAR(TODAY()) - YEAR('Customer Lookup'[birthdate]) - 1, // Case when this year's birthday happened already MONTH(TODAY()) > MONTH('Customer Lookup'[birthdate]) || MONTH(TODAY()) = MONTH('Customer Lookup'[birthdate]) && DAY(TODAY()) >= DAY('Customer Lookup'[birthdate]), YEAR(TODAY()) - YEAR('Customer Lookup'[birthdate]) ) Thank you!

[@SQLBI]

Read this blog post about that specific problem (the video was about the DAX functions only): www.sqlbi.com/blog/marco/2018/06/24/correct-calculate-of-age-in-dax-from-birthday/