cancel
Showing results for 
Search instead for 
Did you mean: 
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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors