Forum Discussion

romovaro's avatar
romovaro
Responsive 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 ...
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"

    )

jgeddes's avatar
jgeddes
Super User
3 years ago

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

should pull the earliest date for each CUID

romovaro's avatar
romovaro
Responsive Resident
3 years ago

Thanks

 

Always a "Life saver". it works. Thanks.

I was tring to update your formula to compare the Earliest GLD and the "Actual Go Live" date 

but it seems, is not really showing the right results.

 

 

 

The idea, as previous comments, is to show:

- No Change

- Changed

- Changed + 3

  • romovaro's avatar
    romovaro
    Responsive Resident
    3 years ago

    HI jgeddes,

     

    if I use the formula:

    Inital GLD Changed = IF(RPT10[GLD] = [Earliest GLD], "No Change", "Changed")
     
    It shows correctly if changed or not (But I would like to keep the changed and Changed +3 filter) . I created a new column shwoing Months diff
     
    Months_between = DATEDIFF([Earliest GLD],RPT10[GLD],MONTH) to get the diff and then create the filter based in numbers but it's showing wrong calculation...
     

     


     

     
     
     
    • jgeddes's avatar
      jgeddes
      Super User
      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"

      )