Can someone interpret this measure for me?

[kkirner]

Hi, I've been working with BI for about six months.  I have a pretty good understanding of some basic to moderately intermediate measures.  However, this one was created in our database prior to my arrival.  I'd really like to understand what it is doing.

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

Thank you!

[kkirner]

Nathan, that's exactly the help I needed.  Thank you very much!

[WinterMist]

Excellent!  Glad to help you!

[WinterMist]

Sorry for the typo in the screenshot above. 

2nd column should read "02 Previous Month" (not 02 yearNum").

This is the code for: MONTH (TODAY()) -1

[WinterMist · Accepted solution]

@kkirner 

It often helps me to understand by creating a separate measure for each VAR, and then pull it onto a table.

Then I am able to see what each VAR is doing.  For example...

Since I do not have access to your PBIX, I cannot call SPSRevenue[DisbursedToLedger], but you can do this and easily create similar measures for each of the 2 "CONTAINSSTRING" functions in your "OR" clause.

But here is what I gather from your code so far...

MonthOnlyLedger =
VAR monthNum = --THIS VAR RETURNS THE NUMBER OF THE PREVIOUS MONTH
IF (
MONTH ( TODAY () ) = 1, --IF TODAY'S MONTH = 1 (Are we in January?)
12, --THEN monthNum = 12
MONTH ( TODAY () ) - 1 ) --ELSE monthNum = TODAY'S MONTH - 1 (If we're in July, return "6")
VAR yearNum =
YEAR ( TODAY () - 31 ) --RETURNS THE YEAR AS OF 31 DAYS AGO (Returns Current Year, unless we're in January)
VAR zeroNum =
INT ( CONCATENATE ( 0, monthNum ) ) --NOT SURE WHAT THE PURPOSE IS HERE, BUT IT RETURNS 6
RETURN
OR (
CONTAINSSTRING (
SPSRevenue[DisbursedToLedger],
IF (
monthNum > 9, --IF PREVIOUS MONTH IN (OCT, NOV, DEC)
CONCATENATE ( RIGHT ( yearNum, 2 ), monthNum ), --THEN RETURN (EXAMPLE: "2210" IF 2022 & OCTOBER)
--CONCATENATES RIGHT 2 DIGITS OF YEAR WITH monthNum
CONCATENATE ( RIGHT ( yearNum, 2 ), CONCATENATE ( "0", monthNum ) ) --ELSE RETURN (EXAMPLE: "22
)
),
CONTAINSSTRING ( SPSRevenue[DisbursedToLedger], FORMAT ( TODAY (), "yymm" ) )
)

Regards,

Nathan