Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SachinFG
Frequent Visitor

Measure based on slicer dates

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]))

5 REPLIES 5
SachinFG
Frequent Visitor

@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.

amitchandak
Super User
Super User

@SachinFG , 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]))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
wdx223_Daniel
Super User
Super User

@SachinFG 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]))

 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.

@SachinFG , 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.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.