Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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)
Should be like this, Also better not to iterate over whole table and just iterate over only required columns.
Thank you.
@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
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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 ?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
The data set is so big I can't share that data. So I will accpet this as the solution. Thanks for the support.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |