Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculating Date Difference between 2 or more dates using DAX

Hi. I'm new in to Power BI and I'm currently making a new column that would give me the latency(no. of days between each transaction) for every member (card number).  Our business rule defined the fo...
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Anonymous,

     

    Based on your sample data, I have created .pbix for you. You can download it.

     

    The issue that you got error because the DateDiff() requires only three parameters passed. You can create the calculated column like below:

     

    Diff = var  d=CALCULATE(MAX('Table1'[Transaction Date]),FILTER('Table1','Table1'[Card No.]=EARLIER(Table1[Card No.]) && 'Table1'[Transaction Date]<EARLIER(Table1[Transaction Date])))
return IF('Table1'[Transaction Date]<d,DATEDIFF('Table1'[Transaction Date],d,DAY),DATEDIFF(d,'Table1'[Transaction Date],DAY))

     

    Then create a measure like below to return average of those difference values for each Card No.

     

    Latency = CALCULATE(AVERAGE(Table1[Diff]),FILTER(ALL(Table1),'Table1'[Card No.]=MAX('Table1'[Card No.])))

     

     

     

    Best Regards,
    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi Anonymous,

 

Based on your sample data, I have created .pbix for you. You can download it.

 

The issue that you got error because the DateDiff() requires only three parameters passed. You can create the calculated column like below:

 

Diff = var  d=CALCULATE(MAX('Table1'[Transaction Date]),FILTER('Table1','Table1'[Card No.]=EARLIER(Table1[Card No.]) && 'Table1'[Transaction Date]<EARLIER(Table1[Transaction Date])))
return IF('Table1'[Transaction Date]<d,DATEDIFF('Table1'[Transaction Date],d,DAY),DATEDIFF(d,'Table1'[Transaction Date],DAY))

 

Then create a measure like below to return average of those difference values for each Card No.

 

Latency = CALCULATE(AVERAGE(Table1[Diff]),FILTER(ALL(Table1),'Table1'[Card No.]=MAX('Table1'[Card No.])))

 

 

 

Best Regards,
Qiuyun Yu

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thank you! This worked for me. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi v-qiuyu-msft, in relation to this solved problem, I am now trying to create a segmentation based on the members' latency and Average Ticket Value. 

     

    My segmentation looks something like this: 

     

     

     

    And I want to know how can I do it in DAX given the data set that I currently have. 

     

    Hoping for your response. 

     

    Thank you! 

  • GuywithDAX's avatar
    GuywithDAX
    New Member
    2 years ago

    Thanks u, I have solved my problem.