Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

take the different between top 2 days

Hi Guys,   Could you please help me to resolve this query in DAX. As per below image I want to take the difference between top 2 dates for each pegging key as a measure. Answer should changed ba...
  • Fowmy's avatar
    Fowmy
    4 years ago

    Anonymous 

    Please check now: You can remove the last zero and replace it with blank if you do not want to see the other dates.

    Top 2 Diff = 
VAR __DATES =  TOPN( 2 , CALCULATETABLE( VALUES(Data[CTP Date]) ,  ALLEXCEPT(Data,Data[PeggingKey] )) , Data[CTP Date])
VAR __MIN = MINX(  __DATES, Data[CTP Date] )
VAR __MAX = MAXX(  __DATES, Data[CTP Date] )
VAR __CURRENTDATE = MAX(Data[CTP Date])
RETURN
IF(
    NOT(HASONEVALUE(Data[PeggingKey])) , 
    BLANK(), 
    IF ( __CURRENTDATE IN {__MAX , __MIN},
        DATEDIFF( __MIN , __MAX,   DAY ),
        0
    )
)

      

Anonymous's avatar
Anonymous
Not applicable
4 years ago

This is my Current Calc

date gap = 
var topdate_1 = 
    CALCULATE(
        MAX('data'[CTP Date]),
        ALLEXCEPT('data','data'[PeggingKey])
    )
var Rank_answer = 
    RANKX(
        ALL('data'[PeggingKey]),
        MAX('data'[CTP Date]),,
        DESC
    )
var Topdate_3 = 
    CALCULATE(
        MAX('data'[CTP Date]),
        FILTER(
            'data',
            Rank_answer = 2
        )
    )
return
DATEDIFF(topdate_1,Topdate_3,DAY)