Filter FactTable data dynamically based on date slicer

[PowerOn_DP]

Hello to All,

I have been searching for solution to simple filter condition, have tried a lot of things by looking at different articals nothing worked, now I am confuse which one to give a shot again. Could someone please help?

I have a direct query for the FactStatus , Matrix to show calculated summary and a date slicer to just filter on data in the factStatus

Below is the condition which I have been trying to replicate but no luck

-- SQL

And (StartOn<=@DateTo Or StartOn='1753-01-01' ) And (Archieved > @DateTo Or ARCHIVED='1753-01-01' )

This is the condition I want to applied to FactStatus based on @DateTo  (Max date from Date Slicer)

Any help would be appreciated.

Thank you,

[v-jayw-msft]

Hi @PowerOn_DP ,

Are you want to filter the table by slicer or do the calculation by slicer?

Please show some sample data and expected result to us.

Best Regards,

Jay

[amitchandak · Accepted solution]

@PowerOn_DP , You can create a new measure

measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
return
calculate(countrows(Table), filter(Table, ([StartOn]<=_max || StartOn = date(1753,01,01)) && ([Archieved] >_max Or ARCHIVED=date(1753,01,01) )))