Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 =
Solved! Go to Solution.
@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 )
)
@tamerj1 Thank you very much. I really appreciate the explanation of each variable and the simpler revision.
@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 )
)
User | Count |
---|---|
52 | |
46 | |
20 | |
16 | |
13 |
User | Count |
---|---|
108 | |
56 | |
31 | |
19 | |
18 |