Forum Discussion
Creating Baseline Measure that can be modified using a slicer.
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?
4 Replies
https://drive.google.com/file/d/1ZG_aWMmbssWggX32L6XFKll7iCAVNKTn/view?usp=sharing Here is a link to the PBIX
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".
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.
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.
