topic Display last X weeks based on single week slicer in DAX Commands and Tips

Display last X weeks based on single week slicer

TomBLG — Tue, 17 Aug 2021 09:50:57 GMT

Hello everyone,

I have a YearWeek field in my Calendar table which selects a week's worth of data as seen below:

I'd like to know if it's possible to show the last 12 weeks (Daily data) based off the YearWeek selection. It basically needs to get the last 'Calendar'[Date] for the selected week and get me the 84 days (7 days * 12 weeks) before that time.

This is what my Calendar looks like with the YearWeek custom column:

Thank you so much for taking the time.

Re: Display last X weeks based on single week slicer

TomBLG — Tue, 17 Aug 2021 10:37:07 GMT

I forgot to include an important part. Above the graph there's a table visualisation. The graph will only populate when an item in the table has been selected:

The measure I'm using for the graph values is this:

Metric Selection =
IF (
ISFILTERED('Table2'[MetricName]),
SWITCH(
TRUE(),
VALUES('Table2'[MetricName]) = "Name1", SUM('Daily'[Metric1]),
VALUES('Table2'[MetricName]) = "Name2", SUM('Daily'[Metric2]),
VALUES('Table2'[MetricName]) = "Name3", SUM('Daily'[Metric3]),
BLANK ()
)
)

And this is as close as I've gotten to getting a 12Week version:

12Week Metric Selection =
IF (
ISFILTERED('Table2'[MetricName]),
SWITCH(
TRUE(),
VALUES('Table2'[MetricName]) = "Name1", CALCULATE(SUM('Daily'[Metric1]), FILTER (ALLSELECTED('Calendar'),'Calendar'[Date]>= MAX ('Calendar'[Date]) - 84 && 'Calendar'[Date] <= MAX ('Calendar'[Date]))),
VALUES('Table2'[MetricName]) = "Name2", CALCULATE(SUM('Daily'[Metric2]), FILTER (ALLSELECTED('Calendar'),'Calendar'[Date]>= MAX ('Calendar'[Date]) - 84 && 'Calendar'[Date] <= MAX ('Calendar'[Date]))),
VALUES('Table2'[MetricName]) = "Name3", CALCULATE(SUM('Daily'[Metric3]), FILTER (ALLSELECTED('Calendar'),'Calendar'[Date]>= MAX ('Calendar'[Date]) - 84 && 'Calendar'[Date] <= MAX ('Calendar'[Date]))),
BLANK ()
)
)

Re: Display last X weeks based on single week slicer

TomBLG — Tue, 17 Aug 2021 12:45:56 GMT

After some trial and error, I managed to get what I wanted by following a Youtube video titled "Show last 6 months based on user single slicer selection" from SQLBI.

12Weeks =

VAR ReferenceDate = MAX ( 'Calendar'[Date] )

VAR PreviousDates =

DATESINPERIOD (

'Previous Date'[Date],

ReferenceDate,

-84,

DAY

)

VAR Result =

CALCULATE(

SUM('Daily'[Metric1]),

REMOVEFILTERS( 'Calendar' ),

KEEPFILTERS( PreviousDates ),

USERELATIONSHIP( 'Calendar'[Date], 'Previous Date'[Date] )

)

RETURN

Result

I still need to change the red section and integrate my "Metric Selection" measure somehow but things are well underway now.

I appreciate the time anyone took looking into this, thank you.

Re: Display last X weeks based on single week slicer

Xy_pbi — Tue, 05 Sep 2023 07:24:23 GMT

Thanks! You are a life saver. That video is so easy to watch and explains step by step logic needed.