MAX date for latest nonblank values based on Slicer's date selection

[ramchoudhary]

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

[v-jayw-msft · Accepted 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

[ramchoudhary]

@Ritesh It is not working - 

My use case is different than the above given solution.

[ribisht17]

@ramchoudhary 

What about this ? https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-get-the-last-non-blank-value-based-on-...

Thanks,

Ritesh

[ribisht17]

@ramchoudhary 

Looks similar to https://community.powerbi.com/t5/Desktop/How-to-get-max-ID-based-on-date-slicer-selection/m-p/205641...

Thanks,

Ritesh