Week Over Week Change in Date

[Anonymous]

Hello.

I have a data set that is primarily activities and dates. Each week we update the data, but I do not replace the old data. Instead, I use the imported file name to generate a work week number. This allows me to see exactly what date each activity fell on each week.
Vastly simplified, the data looks a little like this.

Unique Identifier	Work Week	Activity	Start	Finish
1	WW01	Design	12/15/2021	1/31/2022
2	WW01	Money	2/1/2022	2/18/2022
3	WW01	Construction	2/19/2022	10/5/2022
4	WW01	Complete	10/6/2022	11/15/2022
1	WW02	Design	12/15/2021	2/4/2022
2	WW02	Money	2/7/2022	2/22/2022
3	WW02	Construction	2/25/2022	10/5/2022
4	WW02	Complete	10/6/2022	1/15/2023
1	WW03	Design	12/15/2021	2/4/2022
2	WW03	Money	2/7/2022	6/22/2022
3	WW03	Construction	2/19/2022	6/15/2022
4	WW03	Complete	12/26/2022	1/30/2022

I am trying to create a performance against schedule week over week. I want an indicator that displays every instance of a date change, both pull in or push out. I'm currently using a column for 'push' that indicates a '1' every time a date slips and a 'pull' column that indicates a '1' each time a date pulls in, week over week-- this both provides true/false logic as well as lets me count the number of pulls or pushes.

Unique Identifier	Work Week	Activity	Start	Finish	Start Push	Start Pull	Finish Push	Finish Pull
1	WW01	Design	12/15/2021	1/31/2022
2	WW01	Money	2/1/2022	2/18/2022
3	WW01	Construction	2/19/2022	10/5/2022
4	WW01	Complete	10/6/2022	11/15/2022
1	WW02	Design	12/15/2021	2/4/2022			1
2	WW02	Money	2/7/2022	2/22/2022	1		1
3	WW02	Construction	2/25/2022	10/5/2022	1
4	WW02	Complete	10/6/2022	1/15/2023				1
1	WW03	Design	12/15/2021	2/4/2022
2	WW03	Money	2/7/2022	6/22/2022			1
3	WW03	Construction	2/19/2022	6/15/2022		1		1
4	WW03	Complete	12/26/2022	1/30/2022	1			1

I would also like to indicate change consistency, meaning, I would like to identify consistent pushes 4 weeks in a row, 3 weeks in a row, and 2 weeks in a row.

I have this all built as a work around, but it isn't an ideal solution. My current solution identifies the max work week as the current work week and references the current work week number. This isn't ideal for my situation, because I can't show where we were in past work weeks. I need to be able to produce the visual below.

What I have:

What I want:

How do I best show changes week over week without the current week being fixed? I need to show historical trends, ie an ability to look back at a prior work week's statistics from a later work week. Currently I can't do this because by establishing the current week as the max, all calculations are relative.

[v-xiaotang]

Hi @Anonymous 

You can try the measure like this

link:https://community.powerbi.com/t5/Desktop/Week-over-Week-Change/m-p/243318

If necessary, you can create a week table, then put week num from that table into x-axis.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586. Can also use a VAR instead of EARLIER. Your use case is different but the pattern is the same essentially.

The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous