cancel
Showing results for
Did you mean:
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 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

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.

Highlighted
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))``````

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://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

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!

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.

Highlighted
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))``````

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://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

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!

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!

Announcements

#### Get Ready for Power BI Dev Camp

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

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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