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

     

bhanu_gautam's avatar
bhanu_gautam
Super User
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