Show data up to slicer date

Question

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!

amitchandak · Answer

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]&lt;= _max))
&nbsp;
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-bihttps://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#M184Appreciate your Kudos.

anonymous · Answer

Hi&nbsp;Anonymous ,
&nbsp;
Can you show some sample data and expected result to us so that we can check if our formula is worked?
&nbsp;
Best Regards,
Jay

Forum Discussion

Show data up to slicer date

2 Replies

Related content

Reverse Slicer not showing all data

Show slicers dynamically

Slicer data

Slicer not showing correct data

Filter data using slicer