Slicer to set tolerances for calcuation

[vipett]

I have a table with a lot of orders with their respective Estimated shipping dates and actual delivery dates and today I have a report that shows which orders are shipped on time (or before). Below is the calculated formula I have for this 

OnTime = IF(OR(SalesOrdersDist[Requesteddate]=0,SalesOrdersDist[Earliest delivery date]=0),"",IF(SalesOrdersDist[Earliest delivery date].[Date]<SalesOrdersDist[Requesteddate].[Date],"Yes","no"))

This works fine, however, I would like to have a slicer where the user can select exact same shipping date (or earlier) or consider everything shipped 2 days later, 5 days later or 7 days later to be on time.

Of course I can create three new columns for this, but I would like to have it more dynamic as the same principle might apply to other places in my report.

Essentially it could look like this:

OnTime+2 = IF(OR(SalesOrdersDist[Requesteddate]=0,SalesOrdersDist[Earliest delivery date]=0),"",IF(SalesOrdersDist[Earliest delivery date].[Date]<SalesOrdersDist[Requesteddate].[Date]+2,"Yes","no"))

but I would like to avoid that...

[v-jayw-msft · Accepted solution]

Hi @vipett ,

You will need to create a table contains 2, 5 and 7 as a column then use this column as slicer.

Using selectedvalue() function could get the selected slicer value.

https://docs.microsoft.com/en-us/dax/selectedvalue-function .

At last modify the formula change "+2" to "+selectedvalue('slicer'[days])".

Best Regards,

Jay

[Greg_Deckler]

@vipett Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[vipett]

The table looks like this 

SalesOrderNumber	Number of deliveries	Earliest delivery date	Last delivery date	Earliest Estimated date	Last Estimated date	Ttl backorder	OT	OTIF	Requesteddate
601105	1	2021-11-15 00:00	2021-11-15 00:00	2021-11-19 00:00	2021-11-19 00:00	0	Yes	Yes	2021-11-19 00:00
600468	1	2021-09-24 00:00	2021-09-24 00:00	2021-09-24 00:00	2021-09-24 00:00	0	no	no	2021-08-23 00:00
600988	1	2021-11-02 00:00	2021-11-02 00:00	2021-11-05 00:00	2021-11-05 00:00	0	no	no	2021-10-22 00:00
600982	1	2021-11-02 00:00	2021-11-02 00:00	2021-11-05 00:00	2021-11-05 00:00	0	no	no	2021-10-22 00:00
601188	1	2021-11-10 00:00	2021-11-10 00:00	2021-11-05 00:00	2021-11-05 00:00	0	no	no	2021-11-05 00:00
600221	1	2021-07-27 00:00	2021-07-27 00:00	2021-07-27 00:00	2021-07-27 00:00	0	no	no	2021-07-23 00:00
600420	1	2021-08-14 00:00	2021-08-14 00:00	2021-08-13 00:00	2021-08-13 00:00	0	no	no	2021-08-13 00:00
600943	1	2021-10-08 00:00	2021-10-08 00:00	2021-10-08 00:00	2021-10-08 00:00	0	no	no	2021-10-08 00:00
600437	1	2021-09-09 00:00	2021-09-09 00:00	2021-09-07 00:00	2021-09-07 00:00	0	no	no	2021-08-19 00:00
600777	1	2021-09-27 00:00	2021-09-27 00:00	2021-09-27 00:00	2021-09-27 00:00	0	no	no	2021-08-26 00:00

OT is calculated like this:

OT = IF(OR(SalesOrdersDist[Requesteddate]=0,SalesOrdersDist[Earliest delivery date]=0),"",IF(SalesOrdersDist[Earliest delivery date].[Date]<SalesOrdersDist[Requesteddate].[Date],"Yes","no"))

OTIF is virutally the same, but instead looks at Last delivery date instead of earliest..

So when I create a table in my report, and look at the share between "Yes" and "No", I get a feeling for how much is delivered on Time. 

But users want to add a tolerance, for instance that we say that deliveries 7 days after requested is still ok, then order 600221 in the example above would be considered on time.

But some areas have +7 days as OK, some areas have +0 days as ok, some have +2, etc etc.

So instead of calculating many columns with all tolerances built in, I would like to have a matrix in my report that shows what is the On Time performance, originally it is with +0 days as accepted, but there should be a slicer where you can click on 2, 5, 7,etc to change the tolerance.

Almost like a what-if scenario - "Ok my On Time performance is 50%, but what if I consider everything shipped no later than 2 days after the requested date as On time", and then the user changes the slicer, and the numbers are updated.