Forum Discussion
Need Help with Measure
Hi Anonymous ,
Your first measure is evaluated in the context of the second measure. If the second measure is evaluated in a filtered context based on for example Period = 2, then the first measured will evaluate to 2 and not to the overall MAX of period in the table.
I think this will remedy this for you:
Current Report Month:=CALCULATE( MAX(CapitalReportData[PERIOD]), FILTER(ALL(CapitalReportData),CapitalReportData[COST TYPE]="PO") )By using ALL() in your FILTER(), the evaluation context is changed and now this measure will always result in the same value, regardless of the context it is evaluated in (e.g. a matrix visual for example).
Hope that this makes sense, let me know if this helped you!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
The 2nd way isn't working because you don't have a separate date table 😉
More specifically, in the second measure...
YTDBudget :=
CALCULATE(
SUM( CapitalReportData[FY BUD] ),
FILTER(
CapitalReportData,
CapitalReportData[PERIOD] <= [Current Report Month]
)
)
...when the first measure i.e. [Current Report Month] gets executed from within the second measure, it's being executed in a nested row-context... so really it looks like this...
YTDBudget :=
CALCULATE(
SUM( CapitalReportData[FY BUD] ),
FILTER(
CapitalReportData,
CapitalReportData[PERIOD] <=
/* first measure */
CALCULATE(
MAX( CapitalReportData[PERIOD] ),
FILTER(
CapitalReportData,
CapitalReportData[COST TYPE] = "PO"
)
)
)
)
A better approach is to use a separate date table with a 1-M relationship to your CapitalReportData table. Then change your measures to the following...
Current Report Month :=
CALCULATE(
MAX( 'Calendar'[PERIOD] ),
FILTER(
CapitalReportData,
CapitalReportData[COST TYPE] = "PO"
)
)
...and...
YTDBudget :=
VAR __curReportMth = [Current Report Month]
VAR __retVal =
CALCULATE(
SUM( CapitalReportData[FY BUD] ),
FILTER(
ALL( 'Calendar') , /* need remove the filter on Period to get YTD */
Calendar[PERIOD] <= __curReportMth
)
)
RETURN __retVal
HTH