Trying to calculate % On Time Delivery for our suppliers

[RickB72]

Hello

First I need to let everyone know I am very new to DAX, so please talk slow.....   lol  

I have a table that contains a list of all suppliers and multiple  receipt dates and I want to calculate the percentage of reciepts that are on Time or Early per supplier based on the total count of the suppliers receipts

I addaed a new column titled "Reciept date - Due Date" to calculate how many days before or after the Due date the shippment arrived.

Then I added a status colum and defined the following 

Status = IF('Supplier OTD'[Reciept date-Due Date] >4,

"Late",

IF('Supplier OTD'[Reciept date-Due Date] <-4,

"Early",

"Ontime"))

Where I'm stumped is how to count the total reciepts per supplier and show the percentage that were Ontime or Early. (Eaxmple:   Totol of 10 receipts for supplier X,  6 were ONTIME, 1 was EARLY and 3 were LATE = 70% Ontime or Early)    I figure I can use a slicer i my visual to filter for a specific time period or suppier .

I have been watching youtube videos try to figure this out but cant really find a similar situation.....  Any help you can provide is greatly appreciated!  ☺

Thanks

Rick   

[Whitewater100]

Hi:

I believe you hould have separate date table and dimension table for Suppliers. Both of these tables only ontain unique values and they have a relationship to your fact table, connecting on both date and Supplier. I will attach a different file that shows a modeling set up like I am referring to. 

To count the early on times a measure can be used.

On Time/Early=
SUMX ( SUMMARIZE ( 'Table', 'Table'[Supplier Name],'Table'[Due Date] ,
'Table'[Reciept Date]), CALCULATE ( IF ( INT('Table'[Reciept Date] - 'Table'[Due Date]> 1, 1 ) ) ))

Total Amt= COUNTROWS('Table')

% on time = DIVIDE([On time/Early], Total Amt],0)

https://drive.google.com/file/d/1QgEO4YwQQh2lqtXk1hnwQYVBuv6BbKcS/view?usp=sharing 

Here is date table code below. You can go to Modeling>New Table

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays

To make your supplier table, just have additional table with only supplier names and an index column. e.g. 1-15.

You can join this table to yuor fact table on Supplier Name(this will work) but better yet you merge your new spllier table with index column and bring just index column into fact table. You can now delete supplier from your fact table and join the supplier table based on the index.

For all visuals use fields from date and new supplier table. 

If you have sample file and this is too much, just send it across and I'll do it for you. Thanks..

[RickB72]

Hello @Whitewater100 

Thank you so much for your kind reply!  I tried to enter your first formula but it produced an error and I could not get any furhter,  See below

I dont see a way to send you my file as an attachment?  Do your need my Power BI File and a sample of the source Data file? 

[Whitewater100]

Hi:

It looks like your question is solved, but if not,you can use GoogleDrive,WETRANSFER,DROPBOX by clicking on object above,to right of smiley face. Using a PBI file is generally the easiest to look at and reivew. I'm tied up most of today but can check your file out later if you want. Thanks..

[v-easonf-msft · Accepted solution]

Hi, @RickB72 

Try formulas as below:

Early/Ontime records = CALCULATE(COUNT('Table'[Supplier Name]),FILTER(ALLEXCEPT('Table','Table'[Supplier Name]),'Table'[Status] in {"Early","OnTime"}))

Total records = CALCULATE(COUNT('Table'[Supplier Name]),ALLEXCEPT('Table','Table'[Supplier Name]))

Percentage = 'Table'[Early/Ontime records]/'Table'[Total records] 

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[RickB72]

Hello @v-easonf-msft 

First I would like to thank you very much for your kind reply ☺.   I added the columns and formulas you stated and I now see the % Early/Ontime for each Supplier Name based on all my data rows that go back to Due Date  Jan 1 2020.

However I need the ability to define the time period and see the coresponding "% On Time/Early".  For example just calculate % On Time/Early based on Due Dates in 2021 or YTD 2022 or 1st Quarter 2022 or perhaps just January 2022.   I thought I could accomplish this by simply adding in a slicer for "DUE DATE"   but it does not appear to work.   See screen capture below

 Could you please advise if the selective Due Date range is possibleis and what I need to do to accomplish this?  Thanks again!!