topic Re: I want to see the values before the date selected in the slicer in DAX Commands and Tips

I want to see the values before the date selected in the slicer

Anonymous — Mon, 23 Jan 2023 18:35:19 GMT

I have a measure that brings the maximum date for that particular row. Let's say sold date. One item could be sold many times because it's returned or any other reason.

I want to see the records when I select a date (single selection), that date and previous dates should be included in my matrix kpi

please ask me if you have any questions
thanks

Re: I want to see the values before the date selected in the slicer

amitchandak — Tue, 24 Jan 2023 01:54:47 GMT

Anonymous , If you only need value

You can create a measure like

measure = calculate(Sum(Table[Value]), filter(all(Date), Date[Date]) < Max(Date[Date]) ))

new measure =
var _max = maxx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <=_max))

But you need show date on axis ,then you need slicer on independent table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Re: I want to see the values before the date selected in the slicer

Anonymous — Tue, 24 Jan 2023 17:21:19 GMT

Hello there,

I would appreciate it if you shared pbix file so I can see how it's working

thanks

Re: I want to see the values before the date selected in the slicer

v-jianboli-msft — Wed, 25 Jan 2023 06:21:13 GMT

Hi Anonymous ,

Approve with amitchandak .

Here is my sample:

Please try:

First create a date table for slicer:

Note: The Date table do not have relationship with your fact table.

Then apply the measure to your visual's filter:

Measure = IF(MAX('Table'[Date])<=MAX('Date'[Date]),1,0)

Final output:

Best Regards,

Jianbo Li

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

Re: I want to see the values before the date selected in the slicer

Anonymous — Wed, 25 Jan 2023 15:09:38 GMT

amitchandak thank you.