Forum Discussion
Issues with calculating previous week value
Hi guys,
Apologies that this question is being asked again, I have gone through countless posts asking for help with calculating the previous weeks values however the solutions didn't work and I'm suspecting it could be because my data is structured differently.
I am trying to create a measure that will calculate the total number of occurences (sum of the value column) for the previous week. I have generated a 'week ending' column from the date column and also generated a previous week column which I have been trying to use in my attempts to solve this however I haven't been able to get it to work.
If anyone is able to assist it would be much appreciated . Please see below for a sample of the data, and here is what the intended result should be:
| Row Labels | Sum of Value | Previous Week |
| 1/12/2019 | 12 | - |
| 8/12/2019 | 11 | 12 |
| 15/12/2019 | 29 | 11 |
| 22/12/2019 | 38 | 29 |
| 29/12/2019 | 5 | 38 |
| 5/01/2020 | 14 | 5 |
| 12/01/2020 | 48 | 14 |
| 19/01/2020 | 33 | 48 |
| 26/01/2020 | 37 | 33 |
| 2/02/2020 | 24 | 37 |
| 9/02/2020 | 7 | 24 |
https://drive.google.com/file/d/1vr6wkPT-MnXJoodHSXyAtljvJ0RM8PI2/view?usp=sharing
Thanks in advance.
garethp Please try below measure.
Measure = VAR _previous = MAX(data[PreviousWeek]) RETURN CALCULATE(SUM(data[Value]),data[Week Ending]=_previous)If it resolved your issue please accept post as solution.
The best solution, I found is to have a week rank and use that. But is better that you create a date table and have week start and weekend dates there and create the rank there. Refer both with date table and without date table
Week Rank = RANKX(data,data[Week Ending],,ASC,Dense) Last week Value = CALCULATE(SUM(data[Value]),FILTER(ALL(data),data[Week Rank]=max(data[Week Rank])-1)) Week Rank = RANKX(date,date[Week Ending],,ASC,Dense) Last week Value = CALCULATE(SUM(data[Value]),FILTER(ALL(date),date[Week Rank]=max(date[Week Rank])-1))Link :
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://www.dropbox.com/s/uw5j7kqnzry70dm/Sample%20Data%20last%20week.pbix?dl=0
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/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
4 Replies
garethp Please try below measure.
Measure = VAR _previous = MAX(data[PreviousWeek]) RETURN CALCULATE(SUM(data[Value]),data[Week Ending]=_previous)If it resolved your issue please accept post as solution.
Thanks Anonymous, that worked perfectly.
Can you please tell me what is the purpose of using MAX for the _previous variable? Is it because there are multiple records sharing the same date and we want to only get one?
- amitchandak
Super User
The best solution, I found is to have a week rank and use that. But is better that you create a date table and have week start and weekend dates there and create the rank there. Refer both with date table and without date table
Week Rank = RANKX(data,data[Week Ending],,ASC,Dense) Last week Value = CALCULATE(SUM(data[Value]),FILTER(ALL(data),data[Week Rank]=max(data[Week Rank])-1)) Week Rank = RANKX(date,date[Week Ending],,ASC,Dense) Last week Value = CALCULATE(SUM(data[Value]),FILTER(ALL(date),date[Week Rank]=max(date[Week Rank])-1))Link :
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://www.dropbox.com/s/uw5j7kqnzry70dm/Sample%20Data%20last%20week.pbix?dl=0
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/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinThanks Amit!
