new column for matrix with weekly-live items

[Mike_Mace]

Happy pandemic Forum,

I have a matrix with:

1. A list of 20 deliverables with dates to be delivered (plan sum) and dates they get submitted (sub sum) - 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

Notice two weeks ago (22 Feb) 3 items were missed / not delivered.

Also last week, (01 Mar) 2 items were missed

I am looking to create a new column (plan sum+) to show the missed items again on shifting current week

Going forward, all missed items in any historic week will accumulate and show again in current week under (plan sum+)

For this exercise, new column (plan sum+) would show

-last week (01 Mar): 3 items that were missed 2 weeks ago, iii, iv, v 

-current week (05 Mar) 4 of 5 missed items (as 1of5 was delivered on week 01 Mar), that would be items, iv, v, viii, x

pbi file:

https://we.tl/t-YAji2gr2ei

Snip:

 

[v-cazheng-msft]

Hi @Mike_Mace 

Is your problem solved?

Best Regards

Caiyun Zheng

[Mike_Mace]

Hi @v-cazheng-msft 

No, not yet. Any ideas?

Were you able to download the file? I can send a new link

[v-cazheng-msft]

Hi @Mike_Mace 

I can download the link. But I'm a little bit confused that what results you really need. Can you show me your expected visual in the form of a table?

Best Regards

Caiyun Zheng

[Mike_Mace]

Hi @v-cazheng-msft 

Thank you for looking into this

I am sending a new link with adjusted data as it matters to see which week the calendar is at currently. I am sending the pbi file and a spreadsheet

The spreadsheet first 2x tabs have the data. 

The other 3 tabs have the goal table. One for whenever the current week changes.

• matrix column "plan sum" counts the data from tab "plan date" (columns A&B)
• matrix column "sub sum" counts the data from tab "submission date" (columns A&B)
• matrix column "failed" is a measurement on pbi file. It counts items that were expected for submission but not submitted
• matrix column "plan+" is the goal. when an item is expected to be submitted but fails, then the item will keep appearing on new current week under "plan+" until the item is finally submitted under "sub sum".
• For example, see item "v" expected to be received week 15th March 2021 but failed. It was then expected under "plan+" on week 22nd March 2021 but was not submitted again, hence kept appearing under "plan+" in the future weeks too.

https://we.tl/t-vSNh9ftFZ4

[amitchandak]

@Mike_Mace , You can refer to current week like this

measure =
var _St = today() +-1*WEEKDAY( today() ,2)+1
Var _ed = today() + 7-1*WEEKDAY( today() ,2)
return
calculate(sum(Table[value]), filter(Table, Table[Date] >= _st && Table[End] <= _ed))

Or deal with wow like

Power BI — Week on Week and WTD
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...
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Mike_Mace]

Hi @amitchandak ,

Thanks for the quick reply. Not sure I get the column references you are using there and I am not getting very far.

I've applied the measure to a number of columns on pbi file but no luck. Can you direct a little more on this please.

plan+ =

var _St = today() +-1*WEEKDAY( today() ,2)+1

Var _ed = today() + 7-1*WEEKDAY( today() ,2)

return

calculate(sum('plan date'[plan sum]), filter('Date Calendar', 'Date Calendar'[Date Calendar] >= _st && [failed] <= _ed))