Slippage Report - Comparing different dates vs Current Month date

[romovaro]

HI 

I need help for a formula comparing different dates vs current Date

I have the below table with CUID column (unique customer number) with Actual Go Live Date (Actual GLD Column) and GO Live Date during last week prev month column, GO Live Date during last week +2prev month column & GO Live Date during last week +3prev month column.

In that Case:

Actual Go Live Date = Go Live date as of Current month (december)

GO Live Date during last week prev month column = last week of November

GO Live Date during last week +2prev month column = last week of October

GO Live Date during last week +3prev month column = last week of September

CID	Client Name	Id Country	Country	CUID	Actual GLD	GLD Prev Month Last Week	GLD 2Prev Month Last Week	GLD 3Prev Month Last Week
010065	Client D	AU	Australia	382045	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010065	Client D	AU	Australia	382050	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010109	Client E	FI	Finland	069141	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010136	Client H	FR	France	023941	12/1/2028	12/1/2028	12/1/2028	1/1/2050
010140	Client I	DE	Germany	063153	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010140	Client I	JP	Japan	063154	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010140	Client I	SE	Sweden	063152	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010140	Client I	TR	Turkey	063155	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010151	Client J	AE	United Arab Emirates	381908	12/1/2022	12/1/2022	11/1/2022	11/1/2022
010151	Client J	ES	Spain	382095	12/1/2022	12/1/2022	11/1/2022	10/1/2022
010151	Client J	FR	France	382706	1/1/2023	1/1/2023	1/1/2023	1/1/2023
010152	Client K	PL	Poland	067887	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010177	Client L	AT	Austria	382100	1/1/2023	1/1/2023	1/1/2023	1/1/2023
010177	Client L	CZ	Czech Republic	065106	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010177	Client L	DE	Germany	382103	1/1/2023	1/1/2023	12/1/2022	12/1/2022
010177	Client L	DE	Germany	382105	1/1/2023	1/1/2023	12/1/2022	12/1/2022
010177	Client L	GR	Greece	382101	1/1/2023	1/1/2023	12/1/2022	12/1/2022
010177	Client L	TR	Turkey	065101	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010181	Client M	IT	Italy	068092	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010192	Client N	GR	Greece	382426	12/1/3000	12/1/3000	11/1/2022	11/1/2022
010202	Client O	CA	Canada	382785	1/1/2023	1/1/2023	1/1/2023	12/1/3000
010221	Client P	DE	Germany	382067	12/1/3000	12/1/3000	12/1/3000	12/1/3000
010229	Client Q	JP	Japan	060420	12/1/2022	12/1/2022	11/1/2022	10/1/2022
010233	Client R	FR	France	031567	12/1/3000	12/1/3000	12/1/3000	12/1/3000

The idea is to compare Actual GLD vs (GO Live Date during last week prev month, GO Live Date during last week +2prev month column & GO Live Date during last week +3prev month) To see how many CUIDS were delayed. The number of CUIDs slipped per month is calculated by comparison of the "Actual schedule GLD" with the GLD of the three previous periods.

The idea is to answer questions like %Slippage numb of CUIDS, Number of Projects slipped, etc.

Another Filter I would like to add is if GLD postponed is higher than 3 Months before GLD.

Example

IF CUID with "GO Live Date during last week +3prev month" = 01/Mar/2023 is showing 01/Apr/2023 in Actual GLD Column 

==> More than 3 months compared to today's month.

Any help will be welcome

Thanks

@jgeddes @Mikelytics 

[jgeddes · Accepted solution]

I apologize, there was a fundemental flaw in my logic used in creating the measures.

Please replace the Slippage Days with...

Slippage Days = 
var _slippageDaysPrev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], DAY)
var _slippageDays2Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], DAY) 
var _slippageDays3Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], DAY)
var _minSlippageValue =
MIN(MIN(_slippageDays3Prev, _slippageDays2Prev), _slippageDaysPrev)
var _maxSlippageValue =
MAX(MAX(_slippageDays3Prev, _slippageDays2Prev), _slippageDaysPrev)
var _result =
SWITCH(
    TRUE(),
    _minSlippageValue < 0, _minSlippageValue,
    _minSlippageValue >= 0, _maxSlippageValue
)
Return
_result

and the Slipped >2 Months with...

Slipped >2 Months = 
var _slippedFromPrev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], MONTH)
var _slippedFrom2Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], Month)
var _slippedFrom3Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], MONTH)
var _monthsSlipped =
MIN(MIN(_slippedFrom3Prev,_slippedFrom2Prev), _slippedFromPrev)
var _result =
IF(_monthsSlipped <= -3, "Yes", "No")
Return _result

This should provide the expected results.

[jgeddes · Accepted solution]

You can add conditional criteria to the evaluation...

I amended the Slipped >2 Months Column (changes in bold) so it tests if the any of the three previous GLD dates were 2050/12/1 and then in the result evaluation it tests if the current GLD is 3000/12/1 and if any of the previous GLDs were 2050/12/1 it returns No slippage, otherwise it will evaluate as it did before.

Slipped >2 Months = 
var _slippedFromPrev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], MONTH)
var _slippedFrom2Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], Month)
var _slippedFrom3Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], MONTH)
var _anyOnHold = 
SWITCH(
    TRUE(),
    gldTable[GLD Prev Month Last Week] = DATE(2050, 12, 1), TRUE(),
    gldTable[GLD 2Prev Month Last Week] = DATE(2050, 12, 1), TRUE(),
    gldTable[GLD 3Prev Month Last Week] = DATE(2050, 12, 1), TRUE(),
    FALSE()
)
var _monthsSlipped =
MIN(MIN(_slippedFrom3Prev,_slippedFrom2Prev), _slippedFromPrev)
var _result =
SWITCH(
    TRUE(),
    AND(gldTable[Actual GLD] = date(3000, 12, 1), _anyOnHold = TRUE()), "No",
    _monthsSlipped <= -3, "Yes", 
    "No"
)
Return
_result

[romovaro]

Hi Jgeddes

Thanks for your help. I used your calculations but I have some questions

Example CUIDs highlightes in yellow. Calculations are counting the slippage twice

CUID 062937 was delayed one year between October and November, (-12 Months) but the calculation is showing -24MOnths.

On the other hand, the ones underlined in black are showing correct numbers.

There is only one table. Should calculations

Slippage Days

work in the same way for all CUIDS?

% is also showing wrong number but I can create different measures for that.

Any idea about the slippage Months Calculation? 

[jgeddes · Accepted solution]

I apologize, there was a fundemental flaw in my logic used in creating the measures.

Please replace the Slippage Days with...

Slippage Days = 
var _slippageDaysPrev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], DAY)
var _slippageDays2Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], DAY) 
var _slippageDays3Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], DAY)
var _minSlippageValue =
MIN(MIN(_slippageDays3Prev, _slippageDays2Prev), _slippageDaysPrev)
var _maxSlippageValue =
MAX(MAX(_slippageDays3Prev, _slippageDays2Prev), _slippageDaysPrev)
var _result =
SWITCH(
    TRUE(),
    _minSlippageValue < 0, _minSlippageValue,
    _minSlippageValue >= 0, _maxSlippageValue
)
Return
_result

and the Slipped >2 Months with...

Slipped >2 Months = 
var _slippedFromPrev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], MONTH)
var _slippedFrom2Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], Month)
var _slippedFrom3Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], MONTH)
var _monthsSlipped =
MIN(MIN(_slippedFrom3Prev,_slippedFrom2Prev), _slippedFromPrev)
var _result =
IF(_monthsSlipped <= -3, "Yes", "No")
Return _result

This should provide the expected results.

[romovaro]

HI @jgeddes 

Your formulas are working good. I just have a quick question

Whn a project has Go Live Date 01/01/2050 means that we are not planning to go on with the project. It's undefined. Then we have projects with Go Live Date 12/01/3000 = "Project is On Hold"

According to the formulas a moved from 01/01/2050 (undefined) to 12/01/3000 (On Hold) it's a slippage move but that's not true, because we were of course never planning this project for 2050. 

DO you think there is an easy way to not treat this movement as slippage?

I have a general filter to identify slippage:

CUID Slipped? = IF('3M After'[Diff_Month]>0,"Yes","No")

+ your slipped formulas applied for more than 3 months or btw 1-3 months.

Thanks

[jgeddes · Accepted solution]

You can add conditional criteria to the evaluation...

I amended the Slipped >2 Months Column (changes in bold) so it tests if the any of the three previous GLD dates were 2050/12/1 and then in the result evaluation it tests if the current GLD is 3000/12/1 and if any of the previous GLDs were 2050/12/1 it returns No slippage, otherwise it will evaluate as it did before.

Slipped >2 Months = 
var _slippedFromPrev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], MONTH)
var _slippedFrom2Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], Month)
var _slippedFrom3Prev =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], MONTH)
var _anyOnHold = 
SWITCH(
    TRUE(),
    gldTable[GLD Prev Month Last Week] = DATE(2050, 12, 1), TRUE(),
    gldTable[GLD 2Prev Month Last Week] = DATE(2050, 12, 1), TRUE(),
    gldTable[GLD 3Prev Month Last Week] = DATE(2050, 12, 1), TRUE(),
    FALSE()
)
var _monthsSlipped =
MIN(MIN(_slippedFrom3Prev,_slippedFrom2Prev), _slippedFromPrev)
var _result =
SWITCH(
    TRUE(),
    AND(gldTable[Actual GLD] = date(3000, 12, 1), _anyOnHold = TRUE()), "No",
    _monthsSlipped <= -3, "Yes", 
    "No"
)
Return
_result

[romovaro]

Thanks, @jgeddes, it works perfectly.

Can you help me to update the formula below including the "2050 to 3000 move no slippage"?

Count Slipped CUIDs =

var _vTable =

SUMMARIZE(

'Weekly Slippage 3M',

'Weekly Slippage 3M'[CUID],

"_isSlipped", IF(SELECTEDVALUE('Weekly Slippage 3M'[Diff Month])<0, 1, 0)

)

Return

SUMX(_vTable, [_isSlipped])

or maybe a filter to show only the slipped projects? Currently I am suing this one below but also de "undefined-On Hold" is included.

Slipped? = IF('Weekly Slippage 3M'[Diff Month ASGLD]<0,"Yes","No")

- - - - - - -

Question regarding same report but comparing Go lide date from 3 months ago vs 3 future consecutive months.

Example. I have Sep GLD and I want to compare vs October, November and December GLD.

Slipped >3 Months 2050 =

var _slippedFromPrev =

DATEDIFF('3M After'[Ops Forecast & Actuals], '3M After'[October], MONTH)

 var _slippedFrom2Prev =

DATEDIFF('3M After'[Ops Forecast & Actuals], '3M After'[November], Month)

var _slippedFrom3Prev =

DATEDIFF('3M After'[Ops Forecast & Actuals], '3M After'[December], MONTH)

var _anyOnHold =

SWITCH(

TRUE(),

'3M After'[October] = DATE(2050, 12, 1), TRUE(),

'3M After'[November] = DATE(2050, 12, 1), TRUE(),

'3M After'[December] = DATE(2050, 12, 1), TRUE(),

FALSE()

)

var _monthsSlipped =

MIN(MIN(_slippedFrom3Prev,_slippedFrom2Prev), _slippedFromPrev)

var _result =

SWITCH(

TRUE(),

AND('3M After'[Ops Forecast & Actuals] = date(3000, 12, 1), _anyOnHold = TRUE()), "No",

_monthsSlipped >3, "Yes",

"No"

)

Return

I see that formula is not working ok. It's because I need to change some of the formula below?

var _monthsSlipped =

MIN(MIN(_slippedFrom3Prev,_slippedFrom2Prev), _slippedFromPrev)

thanks

[jgeddes · Accepted solution]

I will give you two new calculated columns. One to calculate slippage days and one to calculate slippage months. They work the same other than the DATEDIFF statement. 

I define a status for each of the previous 3 GLDs. If they are not 'on hold' or 'undefined' then the date difference is taken. Each of the three date differences is then evaluated to find the maximum of each absolute value to return either the slippage days or months.

These formulas should now capture all of your scenarios and you can use them to create additional calculated columns for filters etc.

newSlippageDays = 
var _onHoldDate =
DATE(3000, 12, 1)
var _undefinedDate =
DATE(2050, 1, 1)
var _gldPrevStatus =
SWITCH(
    TRUE(),
    gldTable[GLD Prev Month Last Week] = _onHoldDate, "OnHold",
    gldTable[GLD Prev Month Last Week] = _undefinedDate, "Undefined",
    "OK"
)
var _gld2PrevStatus =
SWITCH(
    TRUE(),
    gldTable[GLD 2Prev Month Last Week] = _onHoldDate, "OnHold",
    gldTable[GLD 2Prev Month Last Week] = _undefinedDate, "Undefined",
    "OK"
)
var _gld3PrevStatus =
SWITCH(
    TRUE(),
    gldTable[GLD 3Prev Month Last Week] = _onHoldDate, "OnHold",
    gldTable[GLD 3Prev Month Last Week] = _undefinedDate, "Undefined",
    "OK"
)
var _prevDiff =
IF(
    _gldPrevStatus = "Ok",
    DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], DAY),
    0
)
var _2PrevDiff =
IF(
    _gld2PrevStatus = "Ok",
    DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], DAY),
    0
)
var _3PrevDiff =
IF(
    _gld3PrevStatus = "Ok",
    DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], DAY),
    0
)
var _slippageDays =
MAX(ABS(_prevDiff),MAX(ABS(_2PrevDiff), ABS(_3PrevDiff)))
Return
_slippageDays

newSlippageMonths = 
var _onHoldDate =
DATE(3000, 12, 1)
var _undefinedDate =
DATE(2050, 1, 1)
var _gldPrevStatus =
SWITCH(
    TRUE(),
    gldTable[GLD Prev Month Last Week] = _onHoldDate, "OnHold",
    gldTable[GLD Prev Month Last Week] = _undefinedDate, "Undefined",
    "OK"
)
var _gld2PrevStatus =
SWITCH(
    TRUE(),
    gldTable[GLD 2Prev Month Last Week] = _onHoldDate, "OnHold",
    gldTable[GLD 2Prev Month Last Week] = _undefinedDate, "Undefined",
    "OK"
)
var _gld3PrevStatus =
SWITCH(
    TRUE(),
    gldTable[GLD 3Prev Month Last Week] = _onHoldDate, "OnHold",
    gldTable[GLD 3Prev Month Last Week] = _undefinedDate, "Undefined",
    "OK"
)
var _prevDiff =
IF(
    _gldPrevStatus = "Ok",
    DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], MONTH),
    0
)
var _2PrevDiff =
IF(
    _gld2PrevStatus = "Ok",
    DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], MONTH),
    0
)
var _3PrevDiff =
IF(
    _gld3PrevStatus = "Ok",
    DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], MONTH),
    0
)
var _slippageMonths =
MAX(ABS(_prevDiff),MAX(ABS(_2PrevDiff), ABS(_3PrevDiff)))
Return
_slippageMonths

[jgeddes]

If you can add calculated columns to your table you could create a calculated column that sums the total slipped days for a CUID. From there you could create measures based on that calculated column to get the count of slipped CUIDs and percentage etc. You could create a second column that determines if the slippage is greater than 3 months and then use that column as the filter you desire.

The calculated column could be...

Slippage Days = 
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], DAY)
+ 
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], DAY) 
+ 
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], DAY)

The count measure...

Count of Slipped CUIDs = 
var _vTable =
SUMMARIZE(
    gldTable,
    gldTable[CUID],
    "_isSlipped", IF(SELECTEDVALUE(gldTable[Slippage Days])<0, 1, 0)
)
Return
SUMX(_vTable, [_isSlipped])

The percentage measure...

Percent CUIDs Slipped = 
var _allRows =
CALCULATE(COUNTROWS(gldTable), ALL(gldTable))
Return
DIVIDE([Count of Slipped CUIDs],_allRows,0)

the slipped three months or more column...

Slipped >2 Months = 
var _monthsSlipped =
DATEDIFF(gldTable[Actual GLD], gldTable[GLD Prev Month Last Week], MONTH)
+ 
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 2Prev Month Last Week], Month) 
+ 
DATEDIFF(gldTable[Actual GLD], gldTable[GLD 3Prev Month Last Week], MONTH)
Return
IF(_monthsSlipped <= -3, "Yes", "No")

The end result...