Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
3 years ago
Solved

Slippage Report - Comparing different dates vs Current Month date

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 

 

Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    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's avatar
    jgeddes
    3 years ago

    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
  • jgeddes's avatar
    jgeddes
    3 years ago

    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

     

7 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    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...

     

  • romovaro's avatar
    romovaro
    Responsive Resident
    3 years ago

    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's avatar
      jgeddes
      Super User
      3 years ago

      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's avatar
        romovaro
        Responsive Resident
        3 years ago

        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