Measure for Previous Week Based on Index

[MightyMicrobe]

Hi all, I have a table with a numeric index indicating Weeks Back from today. 

I need to create a measure indicating the value of the previous week, based on the index. 

I can successfully do it in a column, using the formula below, but I want to do it as a measure. 

I tried building it with Quick measures, but only seem to be able to do it from week 0. How do I modify the formula below so that it references the previous week instead of week zero? Thank you!

[Tahreem24]

@MightyMicrobe ,

If you want it to use in measure so use MAX instead of EARLIER. Because EARLIER is not supported in measure.

[MightyMicrobe]

Yes, this is the bit that threw me -- I didn't think of using MAX/MIN instead of EARLIER. 

My final formula is this:

Total User Previous Period = 
CALCULATE([Total Users], 
FILTER(ALL('Asset Customer Activity'), 'Asset Customer Activity'[Weeks Back]=MIN('Asset Customer Activity'[Weeks Back])+1))

I do have a calendar table, but for this particular query I'm using the index already there (long story). 

[amitchandak]

@MightyMicrobe , refer to my blog , how to deal with week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Using

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[MightyMicrobe]

@amitchandak Thank you for your help, this formula works. 

I have a further question if you don't mind. I'm trying to use this formula (the previous week results) in a KPI visualisation. The report has some slicers/filters. They apply to the main data label of the visualization, but not to the previous week's results based on your formula. 

Can you give a hint on how to modify the formula so that it references the filters on the page or at the viz level?

Total User Previous Period = 
CALCULATE([Total Users], 
FILTER(ALL('Asset Customer Activity'), 'Asset Customer Activity'[Weeks Back]=MIN('Asset Customer Activity'[Weeks Back])+1))

[MightyMicrobe]

This is my data:

[Greg_Deckler]

So typically you do this using ADDCOLUMNS like:

Measure = 

VAR __Table = ADDCOLUMNS('Table',"Previous",<your column formula goes here>)

RETURN

<some operation across __Table>

If that is not enough detail for you. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.