Forum Discussion
Measure for Previous Week Based on Index
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!
6 Replies
If you want it to use in measure so use MAX instead of EARLIER. Because EARLIER is not supported in measure.
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).
This is my data:
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.
MightyMicrobe , refer to my blog , how to deal with week
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/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))
