cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## take the different between top 2 days

Hi Guys,

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.

Power BI File attched here

1 ACCEPTED SOLUTION
Super User

@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
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])
)
RANKX(
ALL('data'[PeggingKey]),
MAX('data'[CTP Date]),,
DESC
)
var Topdate_3 =
CALCULATE(
MAX('data'[CTP Date]),
FILTER(
'data',
)
)
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.

Super User

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

Super User

@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
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 date and other can be blank or zero as well.

Super User

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

Super User

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

Announcements

#### 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 Design Challenge

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

#### 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
Top Kudoed Authors