Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
7 years ago
Solved

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.

Need Help

6 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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

     

     

    • Arnault_'s avatar
      Arnault_
      Resolver III
      7 years ago

      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 :

       

       

      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,

       

       

       

       

    • Arnault_'s avatar
      Arnault_
      Resolver III
      7 years ago

      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

       

       

    • Arnault_'s avatar
      Arnault_
      Resolver III
      7 years ago

      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