Show data up to slicer date

[Anonymous]

Hello everyone! I hope someone can help me with a problem I'm having.

So, the purpose of my report is to show the latest status of all cards by modified date up to the date in a slicer.

My formula works fine if I choose all days in a list slicer or I choose a date range in a slicer with 2 dates (where the starting day is the beginning of report and the last day is today or any other date). Since in our reports we don't use date slicers with two dates due to their inconvinience, it is only accepted to use single-select drop-down slicer. So, I need to change my dax formula to show all information up to today's date (or any date in single select slicer), in other words to imitate date slicer with a range.

For date slicers i use a separate dim_date table. My dax formula is the following:

Latest card =

var Max_Date= CALCULATE(MAX(report[Modified]);FILTER(ALLSELECTED(report); report[Cardname]=MAX(report[Cardname]))) return

CALCULATE(MAX(report[Cardname]); FILTER('report';report[Modified]= Max_Date)

To put this all into perspective, I'd use this formula as a value within a matrix, where rows would be an index and columns -status names. Usually I use something like date<= max (date) but it didn't work. It will be great if i could keep this formula just with an addition of a date range component.

Thanks in advance, if you need some additional info I will be happy to provide. Cheers!

[Anonymous]

Hi @Anonymous ,

Can you show some sample data and expected result to us so that we can check if our formula is worked?

Best Regards,

Jay

[amitchandak]

@Anonymous , prefer a date table

Measure =
var _max = maxx(allselected('Date'),'Date'[Date])
return 
 calculate(LASTNONBLANKVALUE(report[card],SUM(Sheet1[Date])), filter(all('Date'),'Date'[Date]<= _max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.