Forum Discussion

balreenBDO's avatar
balreenBDO
Frequent Visitor
3 years ago

Totals calculating incorrectly

Hello, I have the following formula which determines whether actuals or forecast should populated the months column. Each individual month column returns correct values but the total column returns incorrect values. How can I fix this in my matrix table?

EOY Comparative =
Var selectionMonth = SELECTEDVALUE('Calendar Date To'[FiscalMonthName])
Var selectionMonthNumber = SWITCH(UPPER(selectionMonth), "JAN", 1, "FEB", 2, "MAR", 3, "APR", 4, "MAY", 5, "JUN", 6, "JUL", 7, "AUG", 8, "SEP", 9, "OCT", 10, "NOV", 11, "DEC", 12, BLANK())
Var selectionYear = IF (selectionMonthNumber >=7,
                    YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year]))-1,
                    YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year])))
Var slicerYYMM = selectionYear& "-" & Format(selectionMonthNumber,"00")
var selYear = Max(RawData[FP])
Var Outcome = CALCULATE(IF(
        DATEVALUE(selYear) <= DATEVALUE(SlicerYYMM),
        [SumAct Formatted MTD],
        [EOY Month Value]
    ), YEAR('Calendar'[Fiscal Year]) = YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year])))
Return
    Outcome

EOY Month Value =
Var selectionMonth = SELECTEDVALUE('Calendar Date To'[FiscalMonthName])
Var selectionMonthNumber = SWITCH(UPPER(selectionMonth), "JAN", 1, "FEB", 2, "MAR", 3, "APR", 4, "MAY", 5, "JUN", 6, "JUL", 7, "AUG", 8, "SEP", 9, "OCT", 10, "NOV", 11, "DEC", 12, BLANK())
Var selectionYear = IF (selectionMonthNumber >=7,
                    YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year]))-1,
                    YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year])))
Var slicerYYMM = selectionYear& "-" & Format(selectionMonthNumber,"00")
Var _CD = CALCULATE([EOY Cummalative Days], ALL('Calendar'[Date]), 'Calendar'[Date] >= DATEVALUE(slicerYYMM) && 'Calendar'[Date]<= DATEVALUE(slicerYYMM))

Var _Var = ([YTD SUM EOY])/ _CD * [EOY Number of Days in Month]
Var _FormattedVar =
IF(
        _Var <> 0,
        FORMAT(
            _Var,
            SWITCH(
                SELECTEDVALUE('Selections Slicer Figures Format'[Value2]),
                "$","#,0;(#,0)",
                "$ with Decimals","#,0.00;(#,0.00)",
                "$'000","#,0;(#,0)",
                "$m","#,0.0;(#,0.0)"
        )

    ),0)
RETURN
    _FormattedVar


Expected Output:
The underlying DAX measure works in slotting in the Actual [SumAct Formatted MTD] and Forecast values [EOY Month Value] but the totals (in orange) will return incorrect values as it is unable to correctly add on the calculated forecast values [EOY Month Value]. 

 ActualActualForecastForecastForecastForecastForecastForecastForecastForecastForecastForecast 
 JulyAugustSeptemberOctoberNovemberDecemberJanuaryFebruaryMarchAprilMayJuneTotals
A525227303030303030303030401
A116169101010101010101010131
A216169101010101010101010131
A320209101010101010101010139
Need Help
Tips and Tricks

12 Replies