Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need to to get the value of latest "as of date" from the below dataset with below conditon.
The lastest "as of date" date should be less than or equal to the selected date in the slicer and within a same deal_ID. So for each deal_ID, I will have a "MAX as of date".
Here is the sample data:
deal_id | values | as of date |
1234 | 30 September 2022 | |
1234 | 19000 | 31 August 2022 |
1234 | 20000 | 31 July 2022 |
1235 | 30 June 2022 | |
1235 | 30000 | 31 May 2022 |
1235 | 40000 | 30 April 2022 |
1235 | 31 March 2022 | |
1236 | 23000 | 28 February 2022 |
1236 | 21000 | 31 January 2022 |
So as per the above data -
if I select
Date Slicer: 30-Sep-2022
Deal ID Slicer: 1234
my output should be: 19000
if I select
Date Slicer: 31-May-2022
Deal ID Slicer: 1235
my output should be: 30000
So, basically I need to have first non blank values where MAX "as of date" is less than or equal to the slicer date within a same deal id.
Thanks,
RC
Solved! Go to Solution.
Hi @ramchoudhary ,
Check the measure:
Measure =
var max_date = CALCULATE(MAX('Table'[as of date]),FILTER(ALL('Table'),'Table'[as of date]<=SELECTEDVALUE('Table'[as of date])&&'Table'[deal_id]=SELECTEDVALUE('Table'[deal_id])&&'Table'[values]<>BLANK()))
return
CALCULATE(SUM('Table'[values]),FILTER(all('Table'),'Table'[as of date] = max_date))
It is recommended to create independent slicers to avoid using ALL() function in formula.
Best Regards,
Jay
Hi @ramchoudhary ,
Check the measure:
Measure =
var max_date = CALCULATE(MAX('Table'[as of date]),FILTER(ALL('Table'),'Table'[as of date]<=SELECTEDVALUE('Table'[as of date])&&'Table'[deal_id]=SELECTEDVALUE('Table'[deal_id])&&'Table'[values]<>BLANK()))
return
CALCULATE(SUM('Table'[values]),FILTER(all('Table'),'Table'[as of date] = max_date))
It is recommended to create independent slicers to avoid using ALL() function in formula.
Best Regards,
Jay
What about this ? https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-get-the-last-non-blank-value-based-on-...
Thanks,
Ritesh
Looks similar to https://community.powerbi.com/t5/Desktop/How-to-get-max-ID-based-on-date-slicer-selection/m-p/205641...
Thanks,
Ritesh
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |