Forum Discussion

romovaro's avatar
romovaro
Icon for Responsive Resident rankResponsive Resident
4 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,

 

One last thing. I have been asked if I can highlight the Changes +3months. Meaning that The change from the previous month has been equal to or greater than 3 months. It's possible?

 

And one question about the measure. I am trying to use the measure also as a filter to show only the ones that "Changed". But it seems filter is not showing all of the changes when I select more than one month...any idea why?

 

Filter applied: (as Example, June shows 33.461$.

 

 

 

But when I select only One month (example June) , then it shows all of them.

 

 

Thanks

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

For this case we will need to change from a measure to a calculated column.
In your RPT10 table add the calculated column;

Changed GLD? =
var _currentPeriod =
RPT10[Period]
var _currentGLD =
FORMAT(RPT10[GLD], "YYYY-MM")
var _prevPeriod =
FORMAT(EOMONTH(_currentPeriod,-1), "YYYY-MM")
var _prevGLD =
FORMAT(LOOKUPVALUE(RPT10[GLD],RPT10[Period Column],_prevPeriod, RPT10[CUID], RPT10[CUID]), "YYYY-MM")
var _gldDifference =
abs(DATEDIFF(DATEVALUE(_currentGLD),IF(OR(ISBLANK(_prevGLD),_prevGLD=""),DATEVALUE(_currentGLD),DATEVALUE(_prevGLD)),MONTH))
Return
IF(
    // test for beginning for date list
    ISBLANK(_prevPeriod) || _prevPeriod = "",
    "No Change",
    IF(
        _gldDifference = 0,
        "No Change",
        IF(
            _gldDifference < 3,
            "Changed",
            "Changed+3"
        )
    )        
)
 
You now use the calculated column in the matrix visual instead of the measure. You can then use that column as a slicer to choose which items you want to see.