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.
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!
Solved! Go to Solution.
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! 🙂
Proud to be a Super User!
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! 🙂
Proud to be a Super User!
One more question on this... Is there a way for me to see what the Filter was returning in my original formula? I think that might help me better understand the fix.
Thanks!
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!
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
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |