Forum Discussion

kkirner's avatar
kkirner
Icon for Helper II rankHelper II
3 years ago
Solved

Cannot understand this measure I inherited

Hi, I inherited this measure from someone who was building reports for our company before I started.  When used as a filter it seems to limit the data returned to revenue items for the current month or the previous month.   Would anyone be kind enough to try to interpret it for me?

MonthOnlyLedger =

VAR monthNum = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY())-1)
VAR yearNum = YEAR(TODAY()-31)
VAR zeroNum = INT(CONCATENATE(0, monthNum))
Return
OR(CONTAINSSTRING(SPSRevenue[DisbursedToLedger], IF(monthNum > 9, CONCATENATE(RIGHT(yearNum,2), monthNum), CONCATENATE(RIGHT(yearNum,2), CONCATENATE("0", monthNum)))), CONTAINSSTRING(SPSRevenue[DisbursedToLedger], FORMAT(TODAY(), "yymm")))
calendar
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    kkirner 
    Yes yoy are right. It restricts the calculation for the last two months.

    MonthOnlyLedger =
VAR monthNum =
    IF ( MONTH ( TODAY () ) = 1, 12, MONTH ( TODAY () ) - 1 ) --Retrieves the previous Month Number
VAR yearNum =
    YEAR ( TODAY () - 31 ) -- Retrieves the year of the previous month
VAR zeroNum =
    INT ( CONCATENATE ( 0, monthNum ) ) -- This does absolutely nothing. It just returns monthNum and it is not used further in the code.
RETURN
    OR (
        CONTAINSSTRING (
            SPSRevenue[DisbursedToLedger],
            -- This column has the folrmat "YYMM"
            IF (
                monthNum > 9,
                CONCATENATE ( RIGHT ( yearNum, 2 ), monthNum ),
                CONCATENATE ( RIGHT ( yearNum, 2 ), CONCATENATE ( "0", monthNum ) ) --The IF statement is just to tackle the month forrmat (one or two digits)
            )
        ),
        CONTAINSSTRING ( SPSRevenue[DisbursedToLedger], FORMAT ( TODAY (), "yymm" ) )
    )
-- the above means if in this month or previous month return true else false

    Can be simplified as

    MonthOnlyLedger =
VAR ThisYearMonth =
    FORMAT ( TODAY (), "YYMM" )
VAR PreviousYearMonth =
    FORMAT ( EOMONTH ( TODAY (), -1 ), "YYMM" )
RETURN
    OR (
        CONTAINSSTRING ( SPSRevenue[DisbursedToLedger], ThisYearMonth ),
        CONTAINSSTRING ( SPSRevenue[DisbursedToLedger], PreviousYearMonth )
    )

2 Replies

  • kkirner's avatar
    kkirner
    Icon for Helper II rankHelper II
    3 years ago

    tamerj1 Thank you very much.  I really appreciate the explanation of each variable and the simpler revision.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    kkirner 
    Yes yoy are right. It restricts the calculation for the last two months.

    MonthOnlyLedger =
VAR monthNum =
    IF ( MONTH ( TODAY () ) = 1, 12, MONTH ( TODAY () ) - 1 ) --Retrieves the previous Month Number
VAR yearNum =
    YEAR ( TODAY () - 31 ) -- Retrieves the year of the previous month
VAR zeroNum =
    INT ( CONCATENATE ( 0, monthNum ) ) -- This does absolutely nothing. It just returns monthNum and it is not used further in the code.
RETURN
    OR (
        CONTAINSSTRING (
            SPSRevenue[DisbursedToLedger],
            -- This column has the folrmat "YYMM"
            IF (
                monthNum > 9,
                CONCATENATE ( RIGHT ( yearNum, 2 ), monthNum ),
                CONCATENATE ( RIGHT ( yearNum, 2 ), CONCATENATE ( "0", monthNum ) ) --The IF statement is just to tackle the month forrmat (one or two digits)
            )
        ),
        CONTAINSSTRING ( SPSRevenue[DisbursedToLedger], FORMAT ( TODAY (), "yymm" ) )
    )
-- the above means if in this month or previous month return true else false

    Can be simplified as

    MonthOnlyLedger =
VAR ThisYearMonth =
    FORMAT ( TODAY (), "YYMM" )
VAR PreviousYearMonth =
    FORMAT ( EOMONTH ( TODAY (), -1 ), "YYMM" )
RETURN
    OR (
        CONTAINSSTRING ( SPSRevenue[DisbursedToLedger], ThisYearMonth ),
        CONTAINSSTRING ( SPSRevenue[DisbursedToLedger], PreviousYearMonth )
    )