Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
garethp
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
Anonymous
Not applicable

@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

amitchandak
Super User
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 Linkedin

View solution in original post

4 REPLIES 4
amitchandak
Super User
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 Linkedin

Thanks Amit!

Anonymous
Not applicable

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.