Calculate the change in progress from the start of last week to the end of last week

[paulfink]

hi guys,

got a task the other week and having trouble figuring it out.

In my report we are looking at the change in progress for projects, it is currently set up to calculate from the start of last month to the end.

There is already a formula for this and my task is to modify this formula so it is the change in progress from the start of last week to the end of last week

The current calculation is in 2 parts:

Prog at BD = 
LOOKUPVALUE(ProjectProgress[ProgressPercentage],
    ProjectProgress[ProgressDate],
       MAX('Dates'[Date]),ProjectProgress[ProjectNumber],Projects[ProjectNumber])


Change = Projects[Progress%] - Projects[Prog at BD]

The Dates in the date table is one singular date that has the date mid-month of last month, 1 of the current month and mid-month of the current month.

What could I do to modify my formula to make it from the start of last week to the end of last week?

[Anonymous]

Hi @paulfink,

It sounds like a common request that analysis on multiple date fields, you can take a look at the following blog 'start date' and 'end data' part if they meet your requirement:

Before You Post, Read This  

If they not help, please share some dummy data with a similar data structure and expected result to help us clarify your requirement and test coding formula on it.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@paulfink , check this post to get the start of the week and end of the week.

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

This week Vs last week is this blog

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

These should for week start and weekend

Last Week start = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[date] = 'Date'[week start Date]))

Last Week end = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[date] = 'Date'[week end Date]))

or

Last Week start = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[date] = min('Date'[date] )))

Last Week end = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[date] = max('Date'[date] )))

[paulfink]

How can i apply this to my report?