Search code examples
powerbidaxmeasure

How to get the datediff value in ascending order in Power BI?


I have date as per below table. I want to compare the minimum date in the Date table with itself first and the diff a 0 , then min date with next higher date and diff as 1 and then so on till the min date compare with the current date as diff accordingly which is mentioned in (What I want table)

All I was able to get the Datediff in reverse order when I am using the below formula

= DATEDIFF('Table'[Date], TODAY(), DAY) 

I tried to reverse the TODAY() and 'Table'[Date] but all it did is that it appended '-' in front of Datediff values.

Date        Datediff   WhatI want
01/04/2020  28          0
22/04/2020  7           21
23/04/2020  6           22
24/04/2020  5           23
25/04/2020  4           24
26/04/2020  3           25
27/04/2020  2           26
28/04/2020  1           27
29/04/2020  0           28

Solution

  • I assumed that you only want those 2 columns in your table and created the following measure:

    Result = 
    VAR __minDate = CALCULATE( MIN( 'Table'[Date] ), ALLSELECTED('Table'[Date] ) )
    Return DATEDIFF( __minDate, MIN('Table'[Date]), DAY )
    

    This should be the expected result if that is the case:

    enter image description here

    Hope it helps you.