Calculating Days late with specific target times

[Bcomm_student]

Part of an assignment for university is making a table for some data showing delivery days and days late, and we only need to show values for the ones that were actually late. I have this table: 

And i've already made a column for actual delivery days. I can't remember what the basic formula was for tasks like these so all help is appreciated.

[v-saisrao-msft]

Hi @Bcomm_student,

Have you had a chance to review the solution we shared by @Kedar_Pande ? If the issue persists, feel free to reply so we can help further.

Thank you.

[Kedar_Pande]

@Bcomm_student 

Hey no worries, this one's dead simple. Create a measure called Days Late:

Days Late = 
IF(
[Actual Delivery Days] > 
LOOKUPVALUE(
Targets[Target Days], 
Targets[Region], SELECTEDVALUE(Regions[Region])
),
[Actual Delivery Days] - 
LOOKUPVALUE(
Targets[Target Days], 
Targets[Region], SELECTEDVALUE(Regions[Region])
)
)

Put it in your table/matrix. It'll show blank for on-time deliveries, and positive numbers for late ones. Just make sure your Region column connects the fact table to that Targets lookup table properly.

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[cengizhanarslan]

You can calculate Days Late by comparing the actual delivery days with the target delivery days for the region. First, make sure you have a column (or relationship) that gives the Target delivery days for each region. Then create a calculated column like this:

Days Late =
VAR LateDays =
    Data[Actual Delivery Days] - Data[Target delivery days]
RETURN
IF ( LateDays > 0, LateDays, BLANK() )

[Bcomm_student]

Will this work with groups? Grouping them into specific regions was easier and I have my delivery duration column, but I think the issue now was I made groups rather than an actual column.

[ryan_mayu]

@Bcomm_student 

pls provide some sample data and expected output.

[Bcomm_student]

Here's some of my provided/current data: 

And these were the assignment instructions; my question mainly concerned part c and d 

[ryan_mayu]

@Bcomm_student 

1. do the data transform in PQ

then create a column

Column =

var _day='Table'[Delivery Date]-'Table'[Order Date]

var _target=maxx(FILTER('Table (2)','Table'[Customer Province]='Table (2)'[Locations]),'Table (2)'[day])

return if(_day<_target,"early",if(_day=_target,"on time","late"))

pls see the attachment below

[Bcomm_student]

I'm not sure how to send files in replies but I get an error with that formula. Maybe I need to get rid of the Region group from before transforming the data?

https://limewire.com/d/1FQDe#tVCsg7FMKW 

[ryan_mayu]

@Bcomm_student 

i can't edit in your file directly, since it linked to the file on your own pc. pls see the attachment below

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in the Regions table

Target = 1*(LEFT(Regions[Target Days],SEARCH(" ",Regions[Target Days])-1))

Write these calculated columns in the Data table

Diff = 1*('Data'[Delivery Date]-'Data'[Order Date])

Target = CALCULATE(MAX(Regions[Target]),FILTER('Regions','Data'[Customer Province]='Regions'[Location]))

Column 2 = if(Data[Diff]<Data[Target],"early",if(Data[Diff]=Data[Target],"on time","late"))

Hope this helps.