Using Custom Dates for Visuals based on FIltered Report Date

[Kurt_C]

Hello,

I am new to PowerBI and have been reading through documentation and DAX trainings to learn as quickly as possible, but I have yet to find a good way to go about what I am looking to do. Any assistance is appreciated!

The intention for this report is to display 5 visualisations, namely line graphs that display information from the last 5 business days in relation to the filtered date on the report. For example, if the end user selects Tuesday as the date for the report, I want the visualisations to display data from the prior Tuesday, Wednesday, Thursday, Friday, and the Monday (day before).

I've tried several items, but the closest I've come is by building a nested IF statement (yes I'm aware a SWITCH would be cleaner) that does return the date I'm looking for, but when I attempt to use that as the filter for the graph, it doesn't automatically pull the information from that date, it just presents a date filter. Screenshot 1 is what the overall report looks like (you can disregard dates), screenshot 2 is the formula for the first graph, and then screenshot 3 is what the formula looks like as a filter.

The intention is for the report to require as minimal end user interaction as possible, so I don't want them to have to manually select the filter dates for each graph if possible. Thanks again for the assistance!

Screenshot 1:

Screenshot 2:

Screenshot 3:

[Anonymous · Accepted solution]

Hi @Kurt_C ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

CALENDAR = CALENDAR(DATE(2023,1,1),DATE(2023,12,31))

(2) We can create a measure. 

Flag = SWITCH(TRUE(),
MAX('Table'[date])=SELECTEDVALUE('CALENDAR'[Date])-1,1,
MAX('Table'[date])=SELECTEDVALUE('CALENDAR'[Date])-2,2,
MAX('Table'[date])=SELECTEDVALUE('CALENDAR'[Date])-3,3,
MAX('Table'[date])=SELECTEDVALUE('CALENDAR'[Date])-4,4,
MAX('Table'[date])=SELECTEDVALUE('CALENDAR'[Date])-5,5)

(3) Create five line graphs, then filter the visual objects with [Flag]=1/2/3/4/5, and select different dates on the slicer without changing the visual objects again.

Best Regards,

Neeko Tang

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

[Kurt_C]

This worked great thank you. It was odd because the data didn't show until I added the date to the legend, I'll have to explore why that is. Outside of tweaking the formula to ignore weekends this was perfect. Very much appreciated!