Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
78 | |
63 | |
47 | |
17 | |
12 |