Take max from set of dates based on date slicer in power bi

[ramchoudhary]

Hi,

I need to to get the max "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	Desired output
1234		30 September 2022	31 August 2022
1234	19000	31 August 2022	31 August 2022
1234	20000	31 July 2022	31 August 2022
1235		30 June 2022	31 May 2022
1235	30000	31 May 2022	31 May 2022
1235	40000	30 April 2022	31 May 2022
1235		31 March 2022	31 May 2022
1236	23000	28 February 2022	28 February 2022
1236	21000	31 January 2022	28 February 2022

So as per the above data -

if I select

Date Slicer: 30-Sep-2022

Deal ID Slicer: 1234

my output should be: 31 August 2022

if I select

Date Slicer: 31-May-2022

Deal ID Slicer: 1235

my output should be: 31 May 2022

So, basically I need to have first non blank max date "as of date" is less than or equal to the slicer date within a same deal id.

Thanks,

RC

[v-stephen-msft · Accepted solution]

Hi @ramchoudhary ,

Probably you need this calculated column:

Desired output = MAXX(FILTER('Table',[deal_id]=EARLIER('Table'[deal_id])&&[values]<>BLANK()),[as of date])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jgeddes]

 Try the measure 

Output =

var _selectedDate =

SELECTEDVALUE('Table (5)'[as of date])

var _dealID =

LOOKUPVALUE('Table (5)'[deal_id],'Table (5)'[as of date],_selectedDate)

Return  

CALCULATE(

    MAX('Table (5)'[as of date]),

    FILTER(ALL('Table (5)'), 'Table (5)'[deal_id] = _dealID && NOT(ISBLANK('Table (5)'[values])))

)