Forum Discussion
Helper IIICreate a Week to Date Report against Goal
My company manually calculates and distributes this "report" every day:
Each row is for a location. This example is from Thursday, so it has 4 days (week starts on Monday).
On the left is the prior day's performance (Actual) and the daily goal. On the right is the sum of the WTD actual and WTD goal, then the % to goal for the week.
I would like to recreate this functionality in Power BI but I don't know how to calculate the WTD portion. I can manage the single day totals, but I don't know how to calculate the days elapsed for both sum of actual and sum of goal.
Can anyone provide any instructions on how to do this, or if it is even possible?
For comparison, here is Monday's report (1 day elapsed)
Thank you
2 Replies
- amitchandak
Super User
aflintdepm , do you have date column, with help from date table
example
WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[YearWeek]=max('Date'[YearWeek]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
aflintdepmamitchandak Thank you for all of those resources. I have watched your video multiple times. I have the formula translated to my tables, but when I try to add the measure to my visual I get unexpected results:
If I create a matrix with my locations as rows and the Current Week measure as a value, I get a blank matrix:
If I use a Table, I get blank results. If I use a card, I get a blank result
However, if I use a matrix with another date field as the column, I get results. I'm not sure why you are able to generate results in your video by just dropping the measure in a table. Any advice?
