cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 based on filters.

 

Please help!

 

Power BI File attched here
https://drive.google.com/file/d/1PThqaYQGn0RvsOdNKwPsVYoC0eYuAJmW/view?usp=sharing

 

 

kazun909_0-1635674680611.png

 

1 ACCEPTED SOLUTION

@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
    )
)

  

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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)
Anonymous
Not applicable

 Should be like this, Also better not to iterate over whole table and just iterate over only required columns.

 

Thank you.

 

kazun909_0-1635675128290.png

 

@Anonymous 

You can use this meaure:

Top 2 Diff = 
VAR __DATES = TOPN( 2 , VALUES(Data[CTP Date]) , Data[CTP Date])
RETURN
IF(
    HASONEVALUE(Data[PeggingKey]),
    DATEDIFF(
        MINX(  __DATES, Data[CTP Date] ),
        MAXX( __DATES , Data[CTP Date] ),
        DAY
    )

)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Thanks this working with just Pegging key. When I bring CTP column to this table results getting wrong.  Could you please fix that as well.

kazun909_0-1635679443983.png

 

@Anonymous 

Not sure why you need to bring in the CTP date here. So, how do you want the difference to be show ? only on the maximum date per Pegging Key and others blank ?

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Yes, Because this is part of full table. Each pegging key have multiple CTP dates. So I want to see the datediff for top 2 CTP dates next to CTP dateAnswer2.png and other can be blank or zero as well.

@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
    )
)

  

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Thank you very much Fowmy. This working so far. Because I have sent just sample data to you. But when I add more feilds like CTP date the results dissapear. Any suggestion to fix that.

 

Thank you.

@Anonymous 

How did you send the file? I am not sure about the issue you are facing, can you save it in one drive or google drive share the link here?  clearly explain where you are not getting the result correct.


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

The data set is so big I can't share that data. So I will accpet this as the solution. Thanks for the support.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors