topic Re: Cannot understand this measure I inherited in DAX Commands and Tips

Cannot understand this measure I inherited

kkirner — Thu, 15 Jun 2023 14:19:47 GMT

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

Re: Cannot understand this measure I inherited

tamerj1 — Thu, 15 Jun 2023 18:39:45 GMT

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

Re: Cannot understand this measure I inherited

kkirner — Thu, 15 Jun 2023 19:14:19 GMT

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