Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KriZo
Resolver I
Resolver I

Exclude data entry for month, if used in earlier month

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()))

 

Udklip.PNG

 

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. 

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

Sorry for much late reply. How do I mask sensitive data?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.