Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Arnault_
Resolver III
Resolver III

Dax Measure using DATEDIFF

Hi, I would like to know what scenario would be the best knowing that I want to :

 

1/ calculate a difference between 2 dates

2/ count the number of occurences against a defined-value

 

I could create a calculated column and do that easely but I want to explore the possibility of creating measures.

Here is what I have done to calculate the difference:

 

DateDifference = VAR date_1 = AVERAGE('FACT Order'[date1]) VAR date_2 = AVERAGE('FACT Order'[date2) RETURN CALCULATE(DATEDIFF(date_1;date_1;DAY) 

It seems to be working. Now I am stuck with the next step where I would like to count the number of occurences which has a value lower than "10" (e.g.). Should I use "addcolumns" to store the results? Thanks in advance for your guidance.

1 ACCEPTED SOLUTION

hi, @Arnault_

For your case, it is just a measure totals problem. Very common. 

It is recommended that you Learn more about the row context in DAX.

https://support.office.com/en-us/article/context-in-dax-formulas-2728fae0-8309-45b6-9d32-1d600440a7a...

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

https://www.microsoftpressstore.com/articles/article.aspx?p=2449191

 

After that, you will understand it easily.

 

 

You could change your visual from clustered column chart to table visual

1.JPG2.JPG

 

So 3 or -8 is the result for the calculation based on whole table.

 

and for your second question, it is the same issue.

You could just use this formula to create a measure

Measure = CALCULATE(COUNTAX('dataset',[On-time shipping (dev) "Average"]))

or 

Measure = CALCULATE(COUNTAX('dataset',[On-time shipping (dev) "Max"]))

Result:

3.JPG

I have had the same problem as you, but now I understand it's very simple. please study these two post well:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

Best Regards,

Lin

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-lili6-msft
Community Support
Community Support

hi, @Arnault_

1. If you want to use datediff in measure, Usually use MAX or MIN function instead of AVERAGE.

DateDifference = VAR date_1 = AVERAGE('FACT Order'[date1]) VAR date_2 = AVERAGE('FACT Order'[date2]) RETURN CALCULATE(DATEDIFF(date_1;date_1;DAY))

2. This looks like a measure totals problem. Very common. See this post about it here:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

you could use COUNTAX() or COUNTROWS() instead of SUMX in that post.

 

If not your case, please share some data sample and expected output. Do mask sensitive data before uploading.

By the way, your post is a bit messy, you could try to change another browser, e.g. IE, Firefox. It will work well.

 

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft,

 

Many thanks for your time and efforts. I do recognize my post was messy and you were kind enough to look at it anyway. It seems Chrome isn't working very well. Problem has been fixed and the post has been updated.

 

I have tested the 2 measures using either "AVERAGE" or "MAX":

AVERAGE

On-time shipping (dev) "Average" =

    VAR TransportDateExpected = AVERAGE('dataset'[date_1])

    VAR TransportDateActual = AVERAGE('dataset'[date_2])

    RETURN CALCULATE(DATEDIFF(TransportDateActual;TransportDateExpected;DAY))

MAX

On-time shipping (dev) "MAX" =

    VAR TransportDateExpected = MAX('dataset'[date_1])

    VAR TransportDateActual = MAX('dataset'[date_2])

    RETURN CALCULATE(DATEDIFF(TransportDateActual;TransportDateExpected;DAY))

If you look at the below picture, you will see that the results are not the same depending on the level of agregation and I can't use the "MAX" function in my scenario. If I undesrtood well the explanations provide in the article (thanks for the link), it is the way it is and aggregation might lead to unexpected results.

 

 

Maybe I need to create a calculated columns.

 

My second question was about the "count" function (or something equivalent). The expected output is the following. In my dataset, the lowest level of agregation is the order line. For each order line I will measure a value. In my scenario, there are 3 distinct values : 10 / -4 / -8.

My objective is to count the number of occurences for each value : "10" =17 / "-4" = 4 / "-8" = 11

 

Do you think this is possible by using only measures?

 

Thanks in advance for your support

 

Pbix file

hi, @Arnault_

For your case, it is just a measure totals problem. Very common. 

It is recommended that you Learn more about the row context in DAX.

https://support.office.com/en-us/article/context-in-dax-formulas-2728fae0-8309-45b6-9d32-1d600440a7a...

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

https://www.microsoftpressstore.com/articles/article.aspx?p=2449191

 

After that, you will understand it easily.

 

 

You could change your visual from clustered column chart to table visual

1.JPG2.JPG

 

So 3 or -8 is the result for the calculation based on whole table.

 

and for your second question, it is the same issue.

You could just use this formula to create a measure

Measure = CALCULATE(COUNTAX('dataset',[On-time shipping (dev) "Average"]))

or 

Measure = CALCULATE(COUNTAX('dataset',[On-time shipping (dev) "Max"]))

Result:

3.JPG

I have had the same problem as you, but now I understand it's very simple. please study these two post well:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

Best Regards,

Lin

 

 

 

 

 

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft,

 

I would like to thank you again for your guidance. This will help a lot. You're right, I definitelly need to learn more about DAX. I will take a look at the articles your shared with me. Thanks a lot

 

Best,

Hi @v-lili6-msft,

 

Many thanks for your time and efforts. I do recognize my post was messy and you were kind enough to look at it anyway. It seems Chrome isn't working very well. Problem has been fixed and the post has been updated.

 

I have tested the 2 measures using either "AVERAGE" or "MAX":

AVERAGE

On-time shipping (dev) "Average" =

    VAR TransportDateExpected = AVERAGE('dataset'[date_1])

    VAR TransportDateActual = AVERAGE('dataset'[date_2])

    RETURN CALCULATE(DATEDIFF(TransportDateActual;TransportDateExpected;DAY))

MAX

On-time shipping (dev) "MAX" =

    VAR TransportDateExpected = MAX('dataset'[date_1])

    VAR TransportDateActual = MAX('dataset'[date_2])

    RETURN CALCULATE(DATEDIFF(TransportDateActual;TransportDateExpected;DAY))

If you look at the charts in the attached pbix, you will see that the results are not the same depending on the level of agregation and I can't use the "MAX" function in my scenario. If I undesrtood well the explanations provide in the article (thanks for the link), it is the way it is and aggregation might lead to unexpected results.

 

Maybe I need to create a calculated columns.

 

My second question was about the "count" function (or something equivalent). The expected output is the following. In my dataset, the lowest level of agregation is the order line. For each order line I will measure a value. In my scenario, there are 3 distinct values : 10 / -4 / -8.

My objective is to count the number of occurences for each value : "10" =17 / "-4" = 4 / "-8" = 11

 

Do you think this is possible by using only measures?

 

Thanks in advance for your support

 

Pbix file

 

 

Hi @v-lili6-msft,

 

First of all, thank you for your reply. My post was "completely messy", and you were kind enough to look at it anyway. It looks like Chrome is not working properly. I am sorry for that. I have updated the post.

 

Thanks also for your advise. I know we normally use MIN and MAX, however I have a problem with the level of aggregation. In my dataset, the lowest level of aggregation is the order line, so if I use the "order id" in a chart, MIN and MAX works perfectly. However, if I want to aggregate the results at the "country" level, the value should be an average.

 

1st measure using "Average"

 

On-time shipping (dev) "Average" = 
    VAR TransportDateExpected = 
        AVERAGE('dataset'[date_1])
    VAR TransportDateActual = 
        AVERAGE('dataset'[date_2])

    RETURN CALCULATE(DATEDIFF(TransportDateActual;TransportDateExpected;DAY))

2nd measure using "MAX"

On-time shipping (dev) "MAX" = 
    VAR TransportDateExpected = 
        MAX('dataset'[date_1])
    VAR TransportDateActual = 
        MAX('dataset'[date_2])

    RETURN CALCULATE(DATEDIFF(TransportDateActual;TransportDateExpected;DAY))

Here are the comparative results :

 

PBI_screen.png

 

My second question is about counting the # of occurences. If you look at the attached file, there are 3 different values (as a result of the 1st formula) : 10, -4, -8. However, if you consider the order lines, these values can be repeated and this is what I want.

The expected output would be :

- number of occurences when the value is "10" = 17

- number of occurences when the value is "-4" = 4

- number of occurences when the value is "-8" = 11

 

Here is attached a pbix file with data and the measure I have created + a picture of my screen displaying the 2 measures (1 using average and 1 using MAX).

 

https://www.dropbox.com/pbix

 

If you think it is better to create a calculated column and then perform counting on it, please tell me.

Best,

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.