Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have made a visual to track a suppliers delivery performance. on two paramtres, both are measured in the sameway, so i will only give example of the one.
It is called OTP (on Time performance). If I have a supplier lead time for 7 days, and they deliver earlier or at most 7 days after this i score it 1, otherwise 0.
I measure the first time an orderline is delivered, hence the use of firstnonblank. And in power query, i've sorted my data based on arrival date, so earliest date is at top.
The OTP formula:
OTP = CALCULATE(SUMX(DISTINCT(DelOrder[OrderLineID]);FIRSTNONBLANK(DelOrder[On Time];0))/DISTINCTCOUNT(DelOrder[OrderLineID]);DISTINCT(DelOrder[OrderLineID]);ALL(DelOrder[Orderline arrival date]))
The formula for the visual:
OTP LineChart = CALCULATE([OTP];DATESBETWEEN(DelOrder[Orderline arrival date];EDATE(TODAY();-12);TODAY()))
My problem occurs, when an orderline is delivered partly. If for instance an orderline is delivered in december, and then again in april the value of 1 will be shown in both month. I would like it not to be included in april. How do I do that? Basically i want my OTP Line chart formula to look at the orderline delivered in april, and ask it self whether there already is an entry for it earlier, if so it should ignorer it for this month. Hope it makes sense.
HI @KriZo,
I'm not so clear your formulas, can you please share some sample data with pbix file for test?
Notice: Please do mask on sensitive data before sharing.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |