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 all,
I'm trying to calculate % variance for a value and leveraging the filter context to decide which year to calculate the variance w.r.t. previous year for. This seems so intuitive it should work and I'm sure it's a silly thing I'm missing. Any ideas?
Solved! Go to Solution.
Hi @rushi
Please try
=
VAR TotaltCO2e = [Total (tCO2-e)]
VAR CurrentYear =
MAX ( 'Calendar Table'[Fiscal Year] )
VAR PreviousYear = CurrentYear - 1
VAR EmissionsOfPreviousYear =
CALCULATE (
SUM ( 'Inventory Data'[Value] ),
'Master Data Elements'[Summary Classification] = "(tCO2-e)",
'Calendar Table'[Fiscal Year] = PreviousYear
)
RETURN
DIVIDE ( TotaltCO2e - EmissionsOfPreviousYear, EmissionsOfPreviousYear )
Hi @rushi
please try
=
VAR TotaltCO2e = [Total (tCO2-e)]
VAR PreviousYear = [Previous fiscal year]
VAR EmissionsOfPreviousYear =
CALCULATE (
//Calculates previous year's Emissions based on filter context
SUM ( 'Inventory Data'[Value] ),
'Master Data Elements'[Summary Classification] = "(tCO2-e)",
'Calendar Table'[Fiscal Year] = PreviousYear // this is what doesn't seem to work
)
RETURN
// return the %variance from previous year
DIVIDE (
TotaltCO2e - EmissionsOfPreviousYear,
EmissionsOfPreviousYear
)
Thanks @tamerj1 ,
While the solution works, it doesn't hold true once the vizual is drilled down into quarters. How can I make the measure so that it holds true for when the visualization has year view but also when it is drilled down into quarter view?
@rushi
Does the [Previous fiscal year] measure work at quarter level? Waht is the code of this measure?
I do not know how to calculate the previous Fiscal year from the current fiscal year in context.
Hi @rushi
Please try
=
VAR TotaltCO2e = [Total (tCO2-e)]
VAR CurrentYear =
MAX ( 'Calendar Table'[Fiscal Year] )
VAR PreviousYear = CurrentYear - 1
VAR EmissionsOfPreviousYear =
CALCULATE (
SUM ( 'Inventory Data'[Value] ),
'Master Data Elements'[Summary Classification] = "(tCO2-e)",
'Calendar Table'[Fiscal Year] = PreviousYear
)
RETURN
DIVIDE ( TotaltCO2e - EmissionsOfPreviousYear, EmissionsOfPreviousYear )
Ah yes! That was simple don't know why I missed that. Thank you @tamerj1 ! It WORKS! Sort of like a manual implementation of SAMEPERIODLASTYEAR.
It could be that when I try to view "Variance in Emissions % Year-on-Year" in a table with fiscal year, for each row, the filter context gets confused between whether to filter by the fiscal year in row or [Previous fiscal year]
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |