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.