Forum Discussion
Compare Weekly History by user selected weeks
Hi PBI Community,
I have a SQL table that is archived weekly and dumped into a table. The setup is roughly like this:
| ID (with duplicates) | Stage | Value | Associated Date | Timestamp of backup |
| 123 | 1 | 100 | 01-01-2020 | 12-01-2020 |
| 123 | 2 | 150 | 01-02-2020 | 19-01-2020 |
| N | N | N | N | N |
It goes weekly, so by the end of the year I'll have 52 rows of the same ID with varying stages, values and dates plus a weekly timestamp.
I have a request for users to be able to select two seperate weeks and compare them in a Power BI report.
| ID | Stage | Value 1. selection | Value 2. selection | Value difference | Associated Date 1 | Associated Date 2 | Date difference |
| 123 | 2 | 100 | 150 | 50 | 01-01-2020 | 01-02-2020 | 31 days |
In this scenario the user would need the option to select both Value 1 and 2 by week and across years maybe.
I've been thinking along the lines of slicer - but that will impact both, whats going to be measures, I guess.
- Can I do a measure and insert a slicer as a filter in a CALCULATE? But not have the slicer affect the table?
- Or create a seperate table with all the week numbers and use a slicer or some sort of user interaction as input to a measure?
Hope anyone has an idea.
Screenshot of the table
- Hi ,
You can make a slicer based on a table of weeks and the selection on one slicer with two selection and then make the measure something similar to this:
VALUE1 =
CALCULATE (
SUM ( Table[Column] );
FILTER ( ALL ( Table[week] ); Table[week] = MIN ( weekstable[week] ) )
)
VALUE2 =
CALCULATE (
SUM ( Table[Column] );
FILTER ( ALL ( Table[week] ); Table[week] = MAX ( weekstable[week] ) )
)
Be aware that if people select more than 1 week it will return the difference between the max and min week so you can add a meaure to say that people should only choose two weeks.
2 Replies
- MFelix
Super User
Hi ,
You can make a slicer based on a table of weeks and the selection on one slicer with two selection and then make the measure something similar to this:
VALUE1 =
CALCULATE (
SUM ( Table[Column] );
FILTER ( ALL ( Table[week] ); Table[week] = MIN ( weekstable[week] ) )
)
VALUE2 =
CALCULATE (
SUM ( Table[Column] );
FILTER ( ALL ( Table[week] ); Table[week] = MAX ( weekstable[week] ) )
)
Be aware that if people select more than 1 week it will return the difference between the max and min week so you can add a meaure to say that people should only choose two weeks.