topic Re: Measure based on slicer dates in DAX Commands and Tips

Measure based on slicer dates

Anonymous — Tue, 03 Nov 2020 01:41:52 GMT

Hi all,

I need help in resolving a issue. There are 2 tables which are connected to each other with 1 to many relationship and a date table.

My tables refreshes every day. I am using a slicer to filter the date. My requirement is to capture the output of say yesterday and it shouldn't change. Means for the date selected in slicer, i should be able to get the status for that day only.

I am using the below dax function to perform the action but it is giving me incorrect output.

Max_Status = calculate(Max(SCH[CO_ST]),ALLEXCEPT(SCH,SCH[ID]))

Re: Measure based on slicer dates

wdx223_Daniel — Tue, 03 Nov 2020 02:13:29 GMT

Anonymous think allexcept function overwrite the filter context comes from date table. try this code

Max_Status = calculate(Max(SCH[CO_ST]),ALLEXCEPT(SCH,SCH[ID]),values(dates[date]))

Re: Measure based on slicer dates

amitchandak — Tue, 03 Nov 2020 02:23:38 GMT

Anonymous , In case you need to save a date like yesterday and today.

Have a column like this in your date table and sort it on the date and save on yesterday

Date Type = SWITCH(TRUE(),'Date'[Date]=TODAY(),"Today",'Date'[Date]=TODAY()-1,"Yesterday",'Date'[Date]&"")

refer:https://www.youtube.com/watch?v=hfn05preQYA

or measure like

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

Re: Measure based on slicer dates

Anonymous — Tue, 03 Nov 2020 02:56:33 GMT

amitchandak I guess I was not able to ask my question correctly. My requirement is if I select say 21st Oct on slicer, I will be able to get the status of the records for that day only and not as on yesterday. Records can have different status on different dates. Status should change based on slicer dates.

Re: Measure based on slicer dates

Anonymous — Tue, 03 Nov 2020 02:57:15 GMT

I guess I was not able to ask my question correctly. My requirement is if I select say 21st Oct on slicer, I will be able to get the status of the records for that day only and not as on yesterday. Records can have different status on different dates. Status should change based on slicer dates.

Re: Measure based on slicer dates

amitchandak — Tue, 03 Nov 2020 06:40:28 GMT

Anonymous , if it is connected with date that should happen.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.