Forum Discussion
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.
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
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!
