Help with Target Dates Please

[Pricey79]

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. 

[Anonymous]

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:

Before You Post, Read This 

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

[amitchandak]

@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.

[Pricey79]

@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])
return

[amitchandak · Accepted solution]

@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...