Forum Discussion
Help with Target Dates Please
Hello, I was hoping someone could help me please.
I have 2 fields in the same query called Target Date and Actual Date. Then I have another field in the same query called Product.
I need to be able to show how many Products hit the target date from the actual date, with a rolling percentage of the week.
So for example, Week 20 has a 78% products on time rate.
Is that possible please?
Thank you in advance. You guys are the best.
Pricey79 , Here I am assuming you add a date table and join that with Actual Date and filter week from it.
for week vs week or week calendar refer
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=pnAesWxYgJ8Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
4 Replies
Pricey79 , There two approach I can think. You have date table and only actual date is joined to it
new measure =
var _max = maxx(allselected('Date'), Date[Date])
var _min = minx(allselected('Date'), Date[Date])
return
divide(count(Table[product]),calculate(count(Table[product]), filter(Table, Table[Target Date] >=_min && Table[Target Date]<=_max)))Second, Target date is also joined to date table as inactive join
new measure =
divide(count(Table[product]),calculate(count(Table[product]), userelationship(Table[Target Date], 'Date'[Date])))
Or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
amitchandak Thank you for your reply.
Apologies, im a novice at this. What Date goes here please?
var _max = maxx(allselected('Date'), Date[Date])
var _min = minx(allselected('Date'), Date[Date])
returnPricey79 , Here I am assuming you add a date table and join that with Actual Date and filter week from it.
for week vs week or week calendar refer
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=pnAesWxYgJ8Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482
Hi Pricey79,
It sounds like a common multiple date fields range analyses requirement, you can take a look at the following blog 'start date', 'end date' part if helps:
In addition, you can also consider creating a calculated table to expand the records in the ranges then you can simply do summarize/aggerate with these fields.
Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community
Regards,Xiaoxin Sheng
