Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mike_Mace
Resolver I
Resolver I

new column for matrix with weekly-live items

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:
 
Snip:
Capture.JPG

6 REPLIES 6
v-cazheng-msft
Community Support
Community Support

Hi @Mike_Mace 

Is your problem solved?

 

Best Regards

Caiyun Zheng

Hi @v-cazheng-msft 

 

No, not yet. Any ideas?

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

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

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

 

Capture 2.JPG

amitchandak
Super User
Super User

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.