Dynamic Last Week Indicator

[Anonymous]

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[jdbuchanan71]

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]

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

[jdbuchanan71]

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]

Sure, 

@Ashish_Mathur  @jdbuchanan71 

Thank you guys, here you have

https://1drv.ms/u/s!Ag7DnkvMsR95pl4ZRjgLE842APhz?e=N4JIin

[jdbuchanan71 · 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

[Anonymous]

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