Forum Discussion

Kurt_C's avatar
Kurt_C
New Member
3 years ago
Solved

Using Custom Dates for Visuals based on FIltered Report Date

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's avatar
    Anonymous
    3 years ago

    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. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Kurt_C
      New Member
      3 years ago

      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!