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"

    )

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

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

Thanks for the Help. I unpivoted the columns in Power Query I created the columns like your screenshot.

 

 

but is not working. 

It says something about Dax comparison operations do not support comparing values of type Date with Text values but I updated all the fileds involved to Date format.

 

Attached a report in case it helps.

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    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"
        )
    )
    • 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.