Percentage of tasks done: Create a measure that takes next week values into account

[Henrik_PBI]

Hi,

My data concists of work orders. For each week I count the number of work orders that are planned for the upcoming week ("planned") and the number of work orders that were planned last week, but not completed ("expired"). My table looks like this:

I need to create a measure that shows the percentage of work orders done, which takes into account the number of work orders that were not completed (shown as expired the next week). 

What I suppose it should look like:

I guess it is an easy solution to this, but I run into trouble when I try to create the measure for week n-1.

Anyone able to help me on this?

[amitchandak · Accepted solution]

@Henrik_PBI ,

Assuming you have task time. Have a separate Week /date table with Week or week Rank.

Column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

measures
This Week = CALCULATE(sum('Table'[Tasks]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])) && Task[Type] = "Expired")
Last Week = CALCULATE(sum('Table'[Tasks]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Percent = Divide([Last Week] -[this Week],[last Week])

refer

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

[Henrik_PBI]

Thank you, @amitchandak  - this seem to work!