Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Comparing Dates (Slippage) vs current Go Live Date Month

HI all,

 

I have the table below showing CUID Number (=project number), GLD (Go Live Date) and the GLD that had this project in prev months.

What I am trying to achieve is to track the changes monthly compared to GLD

Like the Excel formula (CellA1=CellB1) but for all the different months vs GLD...is there any way to automatize this?

 

or Maybe a formula comparing current month vs pre month and current month vs 2 month ago and current month vs 3 months ago...? (Last column it's always going to show current month)

 

See below some highlighted changes.

 

 

For example, ULLCPH01 had 1 Change. Same as ULLCNL02.

XROXDK01 had 2 changes.

 

Another think I would like to achieve is to show if the slippage (change) was done +months prev GLD or not.

 

Thanks for any tip,

 

 

 

Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    Thanks for attaching the report.

    Change the period column to type text then this measure works

    Previous Period Changed? =
    var _currentPeriod =
    SELECTEDVALUE(RPT10[Period])
    var _currentGLD =
    FORMAT(SELECTEDVALUE(RPT10[GLD]), "YYYY-MM")
    var _prevPeriod =
    FORMAT(EOMONTH(_currentPeriod,-1), "YYYY-MM")
    var _prevGLD =
    FORMAT(LOOKUPVALUE(RPT10[GLD],RPT10[Period Column],_prevPeriod, RPT10[CUID], SELECTEDVALUE(RPT10[CUID])), "YYYY-MM")
    Return
    IF(
        // test for beginning for date list
        ISBLANK(_prevPeriod) || _prevPeriod = "",
        "No Change",
        IF(
            _currentGLD = _prevGLD,
            "No Change",
            "CHANGED"
        )
    )
  • jgeddes's avatar
    jgeddes
    3 years ago

    CALCULATE(
    MIN(RPT10[GLD]),
    ALLEXCEPT(RPT10, RPT[CUID])
    )

    should pull the earliest date for each CUID

  • jgeddes's avatar
    jgeddes
    3 years ago

    You should be able to change the Initial GLD Changed measure to

    Inital GLD Changed = 
    SWITCH(
    TRUE(),
    RPT10[GLD] = [Earliest GLD], "No Change",
    DATEDIFF([Earliest GLD], RPT10[GLD], MONTH) >=3, "Changed +3",
    DATEDIFF([Earliest GLD], RPT10[GLD], MONTH) <3, "Changed"

    )

14 Replies

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    3 years ago

    I am not sure that I 100% understand the outcome you are looking for but

    I created a small dataset based on your example as follows

    Using that data I made the following measure

    Previous Period Changed? =
    var _currentPeriod =
    SELECTEDVALUE(gldTable[Period])
    var _currentGLD =
    FORMAT(SELECTEDVALUE(gldTable[GLD]), "YYYY-0M")
    var _prevPeriod =
    FORMAT(DATEADD(gldTable[Period],-1,MONTH), "YYYY-0M")
    var _prevGLD =
    CALCULATE(values(gldTable[GLD]),gldTable[Period Column]=_prevPeriod)
    Return
    IF(
        // test for beginning for date list
        ISBLANK(_prevPeriod) || _prevPeriod = "",
        "No Change",
        IF(
            _currentGLD = _prevGLD,
            "No Change",
            "CHANGED"
        )
    )
    and ended up with this

    Hope this helps.

    • romovaro's avatar
      romovaro
      Icon for Responsive Resident rankResponsive Resident
      3 years ago

      Thanks jgeddes,

       

      Thanks for your answer. That looks good. I see that you combined all dates column in one column called period.

      The thing is that every month I will get the excel file like the one below:

      One column with dates for every month. 

       

       

      Tried different ways to Merge/Group to move all dates in one column based on CUID and create your "period column" but it seems I am unable to do it.

      • jgeddes's avatar
        jgeddes
        Icon for Super User rankSuper User
        3 years ago

        In the format you have you will need to unpivot the columns in Power Query and then you will be able to add the Period column. The period column I created was just done with DAX. FORMAT(gldTable[Period[, "YYYY-MM")