Logical error in calculating value for previous fiscal year

[rushi]

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?

 Variance in Emissions % Year-on-Year =
VAR EmissionsOfPreviousYear =
    CALCULATE (
        //Calculates previous year's Emissions based on filter context
        SUM ( 'Inventory Data'[Value] ),
        FILTER (
            'Master Data Elements',
            'Master Data Elements'[Summary Classification] = "(tCO2-e)"
        ),
        FILTER (
            'Calendar Table'[Fiscal Year],
            'Calendar Table'[Fiscal Year] = [Previous fiscal year] // this is what doesn't seem to work
        )
    )
RETURN
    // return the %variance from previous year
    DIVIDE (
        Metrics[Total (tCO2-e)] - EmissionsOfPreviousYear,
        EmissionsOfPreviousYear
    )

For your information:

Previous fiscal year = MAX('Calendar Table'[Fiscal Year])-1

And,

Total (tCO2-e) =

CALCULATE (

SUM ( 'Inventory Data'[Value] ),

'Master Data Elements'[Summary Classification] = "(tCO2-e)"

)

[tamerj1]

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
    )

[rushi]

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?

[tamerj1]

@rushi 
Does the [Previous fiscal year] measure work at quarter level? Waht is the code of this measure?

[rushi]

I do not know how to calculate the previous Fiscal year from the current fiscal year in context.

[tamerj1 · Accepted 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 )

[rushi]

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.

[rushi]

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]