Forum Discussion
Need Help with Measure
I am having trouble getting a measure to work. I have two measures that I am trying to get to work together. The first calculates the maximum value in a column (which will tell me the month #). And then the second measure is supposed to sum a column for all values <= to the maximum month value (that will tell me the current reporting month.) Here are my measures...
This measure returns the # "1":
Current Report Month:=CALCULATE(
MAX(CapitalReportData[PERIOD]),
FILTER(CapitalReportData,CapitalReportData[COST TYPE]="PO")
)
This measure is returning "0" but it should return the sum of FY BUD for months <=1 (Current Report Month)
YTD Budget:=CALCULATE(
SUM(CapitalReportData[FY BUD]),
FILTER(CapitalReportData,CapitalReportData[PERIOD]<=[Current Report Month])
)
When I re-write the 2nd measure as follows, I get the correct value....
YTD Budget:=CALCULATE(
SUM(CapitalReportData[FY BUD]),
FILTER(CapitalReportData,CapitalReportData[PERIOD]<=1)
)
So my question is, why won't the "YTD Budget" measure return a value when using the "Current Report Month" measure?
Thanks!
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! 🙂
4 Replies
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 __retValHTH
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! 🙂
Thanks for both suggestions. Since the 2nd one was easier to implement, I started with that one and it worked. (Adding ALL to the Filter of the Measure 1. I will need to think about why that worked!
And I understand the point about the date table. The query that generates this data already computes the period based on the date but I guess doing that in the model is a better solution so I will look at that going forward.
Thanks again to both!
