Creating Baseline Measure that can be modified using a slicer.

[BobBiff]

I've been hunting around for a solution to this but I cant seem to get anywhere.

I have data that looks like this (truncated) 

WeekEnding	Period	Count
18/09/2022	1	400
18/09/2022	2	523
18/09/2022	3	447
25/09/2022	1	446
25/09/2022	2	488
25/09/2022	3	440
02/10/2022	1	415
02/10/2022	2	535
02/10/2022	3	435
09/10/2022	1	422
09/10/2022	2	583
09/10/2022	3	484
16/10/2022	1	428
16/10/2022	2	554
16/10/2022	3	407
23/10/2022	1	490
23/10/2022	2	552
23/10/2022	3	476
30/10/2022	1	425
30/10/2022	2	520

I have been asked to create line graph visualisations that look at the Count over the three Periods within a given week.

My manager wants to be able to use a slicer to nominate a "baseline" week, that a second nominated "comparison" week can be compared to. It needs to be a slicer because the baseline week may change. The comparison week is usually (but not always) the most recent week, so will need its own independant slicer as well.

They want to see two lines in the same graph that show how the comparison week varies from the baseline. 

I've tried to use a date table to filter the measure but I kep geting tied up in knots.

Any advice on tools to use to acheive this?

[johnyip]

@BobBiff 

Is this what you want to achieve?

Based on your sample file, you can follow the below steps to construct the above shown in my attached file.

1. Create two calculated table using the below DAX. You may wish to change the data type of the column as Date for beauty purpose.

Baseline Week = SELECTCOLUMNS(DISTINCT('Table'[WeekEnding]),"Baseline Week", [WeekEnding])

Comparsion Week = SELECTCOLUMNS(DISTINCT('Table'[WeekEnding]),"Comparsion Week", [WeekEnding])

2. Create two measures as below.

Baseline = CALCULATE(SUM('Table'[Count]),'Table'[WeekEnding] = SELECTEDVALUE('Baseline Week'[Baseline Week]))

Comparsion = CALCULATE(SUM('Table'[Count]),'Table'[WeekEnding] = SELECTEDVALUE('Comparsion Week'[Comparsion Week]))

3. Use those two columns from the calculated tables in your slicers, and those two measures in your visualization.

[johnyip]

Can you provide some expected outcomes of your visuals for reference? I can ot quite guess what comparsion is done based on the "baseline", and how do you determine the second nominated "comparison week".

[BobBiff]

The aim is that the maximum number of count occurs in the earlier periods. They want to set a baseline for a certain point in the year (e.g. a week in July) and then compare later weeks to that one to see if the distribution is better or worse.

[BobBiff]

https://drive.google.com/file/d/1ZG_aWMmbssWggX32L6XFKll7iCAVNKTn/view?usp=sharing Here is a link to the PBIX