topic Re: Struggling with formula: select all rows, excluding date slicers in Desktop

Struggling with formula: select all rows, excluding date slicers

Conics — Tue, 29 Mar 2022 18:03:19 GMT

Hi,

I'm currently struggling with a formula. so here we go:

This is a photo of my dataset:

Actual excal attached below on the dropbox link.

This means I have an order ORD001 for Art. A with a delivered qty of 5 pcs on line 10 and in the same order a canceled line 20 for 5 pieces of Art. B ordered, but since it has been canceled, none were delivered.

I need to calculate the service level of deliveries:
ORD001- Art. A: 5 out of 5 delivered on 01/03 would mean 100% on a line level;
ORD001- ART. B: 0 out of 5 = 0% and
ORD001-Art. C: 10 out of 10 on 05/03/2022 would be 100% on line level.
while on total ORDER level, that would be 15 delievred out of 20, so 75%.

I also show another order ORD004 with 20 out of 20 delivered pieces for article B, and 0 out of 40 deliverted for ART. B.

That would mean, for Article A 100% service level, for Article B 0%, for the complete order 20 out of 60 delivered = 33%

Now for the tricky part:

We want to use a date slicer (Based on delivery date). This will filter out all rows with that Date, BUT... I really need to take in account the date-less rows as well (where cancellations have been happening).

So when looking back at ORD001:
My service level on 01/03 would be: total ordered 20pieces, delivered: 5 pieces, service level 5 out of 20 = 25%
My Service level on 05/03 would be: total ordered 20pieces, delivered:15 pieces, service level 15 out of 20 = 75%.

The issue is now: how do I calulate the total Ordered, in such a way, that IT ONLY counts the QTY canceled for the orders applied in the date filter. In other words, If I select 01/03/2022, it should look for all orders with a delivery on that date, then calculate lines delivered AND Qty Canceled (for these orders only), and summarize these.

Link to my Dataset and PBIX file

In my file you will see that no matter how much I change the date slicer, the Total Canceled remains the same... so there's some error in my calculated "total ordered"

Re: Struggling with formula: select all rows, excluding date slicers

Anonymous — Fri, 01 Apr 2022 09:41:25 GMT

Hi Conics ,

You need a helper column to tell where the order is when the date is empty.

Add a new index column in Power Query Editor.

Modify the measure.

Total Applied Cancelled Lines = VAR _mindate =CALCULATE(MIN('Blad1'[Index]),FILTER('Blad1','Blad1'[Delivery Date]=MIN('Blad1'[Delivery Date]))) VAR _maxdate =CALCULATE(MAX('Blad1'[Index]),FILTER('Blad1','Blad1'[Delivery Date]=MAX('Blad1'[Delivery Date]))) VAR _value = CALCULATE ( [QTY ordered], FILTER ( 'Blad1', Blad1[Cancelled] = "Yes" && ISBLANK ( Blad1[Delivery Date] ) = TRUE() &&'Blad1'[Index]>=_mindate &&'Blad1'[Index]<=_maxdate ) ) RETURN SWITCH( TRUE(), _value<>0,_value,0)

Filter index columns can help formulas determine whether they are within or outside of this time period.

The result is 5.

Attach the PBIX file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Re: Struggling with formula: select all rows, excluding date slicers

Conics — Mon, 04 Apr 2022 12:15:45 GMT

Anonymous: when moving the slicer dates to 01/04/2022 (both of them) I see ZERO, while I was expecting 40 cancelled?