Compare Weekly History by user selected weeks

[donaldo]

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

[MFelix · Accepted solution]

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.

[donaldo]

Brilliant and simple. Thanks.