Forum Discussion

getsmithed's avatar
getsmithed
Frequent Visitor
1 year ago
Solved

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

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 scre...
"CALCULATE"
"Dates"
REMOVEFILTERS
variance
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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

     

getsmithed's avatar
getsmithed
Frequent Visitor
1 year ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.