cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dynamic Last Week Indicator

Hey,

 

Id like to create a meassure and show it as an indicator for the sales of last week. BUT, what I really want is that the "Last week" is not necessarily 7 days ago. What I need is whenever I use a Week Slicer, the measure always shows me the result one week before the one I selected on the slicer and whenever the Data is not filtered shows me the real last week. 

 

Thank you guys

 

 

 

1 ACCEPTED SOLUTION

Hello @Anonymous 

Because you had a week column in your Calendar table already we are able to just use that.  I added a measure to the .pbix.

QA Score Last Week = 
VAR MaxWeek = CALCULATE(MAX('Calendar'[Week]),QAData)
VAR SelectedWeek = SELECTEDVALUE( 'Calendar'[Week],MaxWeek )
RETURN 
    CALCULATE( [QA Score], 'Calendar'[Week] = SelectedWeek-1 )

And added it to the table visual in your file then uploaded it here:

https://www.dropbox.com/s/0bdr84qdqz6auyv/QA%20Dashboard.pbix?dl=0

 

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jdbuchanan71
Super User
Super User

Hello @Anonymous ,

You can add a WeekOffset column to your date table that will help.

Week Offset = DATEDIFF( TODAY(), Dates[Date], WEEK )

Then you write a measure, something like this.

Last Week = 
VAR SelectedWeek = SELECTEDVALUE( Dates[Week Offset], 0 )
RETURN 
    CALCULATE(
        Sales[Sales Amount],
        Dates[Week Offset] = SelectedWeek-1
    )

If no week is selected the VAR SelectedWeek will give 0 and that will get adjusted in the CALCULATE by -1 giving you last week.  If you do selected a week the CALCULATE will pull the previous week amount.

Anonymous
Not applicable

Hey thank you, i tried to do it but for some reason power bi doesnt let me select any column on the second argument for DATEDIFF, idk if im doing something wrong 😞 Sorry, im new in power bi

Are you adding it as a column in your date table?  Are you able to share your .pbix file (uploade it to dropbox or onedrive and post a link)?

Anonymous
Not applicable

Hello @Anonymous 

Because you had a week column in your Calendar table already we are able to just use that.  I added a measure to the .pbix.

QA Score Last Week = 
VAR MaxWeek = CALCULATE(MAX('Calendar'[Week]),QAData)
VAR SelectedWeek = SELECTEDVALUE( 'Calendar'[Week],MaxWeek )
RETURN 
    CALCULATE( [QA Score], 'Calendar'[Week] = SelectedWeek-1 )

And added it to the table visual in your file then uploaded it here:

https://www.dropbox.com/s/0bdr84qdqz6auyv/QA%20Dashboard.pbix?dl=0

 

Anonymous
Not applicable

Thanks so much for your help!! That exactly what I needed 🙂

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.