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

    )

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive 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
Icon for Super User rankSuper 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"

)