cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 LabelsSum of ValuePrevious Week
1/12/201912-
8/12/20191112
15/12/20192911
22/12/20193829
29/12/2019538
5/01/2020145
12/01/20204814
19/01/20203348
26/01/20203733
2/02/20202437
9/02/2020724

 

 

https://drive.google.com/file/d/1vr6wkPT-MnXJoodHSXyAtljvJ0RM8PI2/view?usp=sharing

 

Thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Issues with calculating previous week value

@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. 

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Issues with calculating previous week value

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Highlighted
Anonymous
Not applicable

Re: Issues with calculating previous week value

@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. 

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Issues with calculating previous week value

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Frequent Visitor

Re: Issues with calculating previous week value

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?

 

Highlighted
Frequent Visitor

Re: Issues with calculating previous week value

Thanks Amit!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors