Variance between Two Years on same Date Axis using same Amount Column

[getsmithed]

Unfiltered:

Filtered:

The task at hand is simple, take the 2025 values (Food Revenue, Beverage Revenue, etc.) and subtract the 2024 values from the correlated field (first screenshot). However, I am running into issues with the date axis it lies on and how to correctly display only the months needed. The formula used takes the sum of the current year (CY, Dec 2024 - Mar 2025) for a specific account, and subtracts it from the balance of the prior year (LY, Dec 2023 - Mar 2024). This does in fact work, as the values shown in the second screenshot are correct for Food Revenue Variance for the months needed thus far (fiscal year starts in December, so December 2024 - March 2025). However, when I filter to just show the months needed, the values revert to only show the balances correlated with the current year (CY). Any ideas on how to show this variance correctly? 

In layman's terms, basically need to INCLUDE data from all months relevant (Dec 2023 - Mar 2024, Dec 2024 - Mar 2025), but I only need to DISPLAY the months with the correct values for variance (Dec 2024 - Mar 2025). 

DAX measure: 
    

VARI Food Revenue =

    VAR CY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = 0)

    VAR LY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = -1, REMOVEFILTERS(DateDim[Date].[Year]))

RETURN

    CY - LY

[getsmithed]

Hi @maruthisp , 

The ConsolidatedReport[GLAccount] is of the text datatype. That is why I'm referencing GLAccount 450000 like "450000". The comparison is between the corresponding months in the amount column (which should be either a decimal or integer), which I can see you have set up in your table already.

[v-pbandela-msft]

Hi @getsmithed,

Thank you @bhanu_gautam , @maruthisp  for the helpful response.

Thanks for confirming the data type for GLAccount — using "450000" is indeed the correct approach since it's a text field.

Given the behavior you described, the updated DAX measure using explicit DATESBETWEEN for both CY and LY (as shared above) is your best approach to ensure:

CY (Dec 2024–Mar 2025) values are displayed.

LY (Dec 2023–Mar 2024) values are still included in the variance calculation, even if not shown

This avoids the issue where visual filters inadvertently remove LY context. Just make sure the measure includes both:

REMOVEFILTERS(DateDim[Date].[Year])

and

DATESBETWEEN(...)

Please continue using Microsoft Community Forum.

If this post helps in resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan.

[bhanu_gautam · Accepted solution]

@getsmithed , Try using

DAX
VARI Food Revenue =
VAR CY = CALCULATE(
SUM(ConsolidatedReport[Amount]),
ConsolidatedReport[GLAccount] = "450000",
DateDim[CurrentFiscalYear] = 0,
DATESBETWEEN(DateDim[Date], DATE(2024, 12, 1), DATE(2025, 3, 31))
)
VAR LY = CALCULATE(
SUM(ConsolidatedReport[Amount]),
ConsolidatedReport[GLAccount] = "450000",
DateDim[CurrentFiscalYear] = -1,
REMOVEFILTERS(DateDim[Date].[Year]),
DATESBETWEEN(DateDim[Date], DATE(2023, 12, 1), DATE(2024, 3, 31))
)
RETURN
CY - LY

[maruthisp]

Hi @getsmithed ,

As per the above DAX expression, what is the below datatypes for ConsolidatedReport[GLAccount]?
I can see there is come DAX error while comparing column with INT with TEXT values.

Variance between Two Years on same Date Axis using same Amount Column.pbix

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X