cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
donaldo Member
Member

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)StageValueAssociated DateTimestamp of backup
123110001-01-202012-01-2020
123215001-02-202019-01-2020
NNNNN

 

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. 

IDStageValue 1. selectionValue 2. selectionValue differenceAssociated Date 1Associated Date 2Date difference
12321001505001-01-202001-02-202031 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

pbi.png

 

 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Compare Weekly History by user selected weeks

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.

Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Compare Weekly History by user selected weeks

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.

Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

donaldo Member
Member

Re: Compare Weekly History by user selected weeks

Brilliant and simple. Thanks.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors