need to find details related to latest date in Policy based on date selected in the Slicer.

[Anonymous]

Hi Guys, I am facing an issue in getting a solution for finding details related to the latest date in policy based on date range selected using the slicer.

Date used. - 

I want to get details related to the latest date when the status is "In Progress" for policy x.

I have made a measure flag 

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),ALLEXCEPT(Sheet1,Sheet1[Policy])),1,0)

this is working fine when the complete date range is selected in the slicer means till 5 June 2022 but when I reduce the slicer range it is not giving data corresponding to 1 Feb 2021 the table is getting blank. Instead, it is giving values related to both 1 Feb 2021 and 4 May 2020. I want only value corresponding to 1 Feb 2021 when Slicer is moved to left a little.

I want the flag to be dynamic based on slicer range.

I am attaching the file PBIX file. 

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Modify it.

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),FILTER(ALLSELECTED(Sheet1),[Policy]=MAX('Sheet1'[Policy]))),1,0)

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Above code is not working.

[amitchandak]

@Anonymous ,

try like

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),ALLSELECTED(Sheet1[Policy])),1,0)

or

Max Flag = IF(MAX(Sheet1[Date]) = CALCULATE(MAX(Sheet1[Date]),ALLSELECTED()),1,0)