Week difference with IFs

Mike_Mace · ‎02-08-2021

Happy lockdowning Forum,

I have a matrix with info:

1. A list of 25 deliverables with random plan dates and random dates they were submitted - these are sourced from two separate spreadsheets connected with a relationship

2. I created (head) columns by "starting-date" week. All random dates are grouped in those weeks

3. To calculate the delay in weeks I used a few variants of the following (which did not get me far)

week diff = DATEDIFF('Date Calendar'[Week start], TODAY(), WEEK) / 7

If "sub sum" is not 1, then the calculation in weeks should be

a. plan date upto current week

or

b. plan date upto week when "sub sum" = 1

Snip shows a good example

File@ https://we.tl/t-37rtvEcn9R

Snip:

Mike_Mace · ‎02-10-2021

Hi @Anonymous ,

This looks like a start to get there.

Making some comments and attaching a snip to back them up (green comments shows answer I'm aiming for)

Rows i/ii: these were submitted on the week they were planned for so i wouldnt expect to see a number showing delays under "week diff" in the following weeks. (in case I confused you, "week diff" should show the delay in weeks from the initial planned date)

Row iii: first week was missed so number 1 is correct. this was submitted on the second week so the number 1 should stop at second week. 3rd week i wouldnt expect to see anything as document iii is completed at week 2

Row iv: this looks like ought to be the other way around, 1st week missed so 1 week delay, 2nd week missed, 2 weeks delay, delivered on 3rd week measure should show 2
Rows v/viii/x: as per (iv) numbers seem to be other way around

Rows vi until last, of which deliverables are expected on 2nd week onwards, they show a delay measure in "week diff" under weeks before they were actually planned. I would excpect to see a delay measure once a deliverable misses its planned week.

From what I see on your model, I wouldn't be able to progress this on my own! Thanks!

Snip: