Totals calculating incorrectly

[balreenBDO]

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]. 

	Actual	Actual	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast	Forecast
	July	August	September	October	November	December	January	February	March	April	May	June	Totals
A	52	52	27	30	30	30	30	30	30	30	30	30	401
A1	16	16	9	10	10	10	10	10	10	10	10	10	131
A2	16	16	9	10	10	10	10	10	10	10	10	10	131
A3	20	20	9	10	10	10	10	10	10	10	10	10	139

[Ahmedx]

Is this what you are looking for?
you need to create a new measure and refer to your measure
https://1drv.ms/u/s!AiUZ0Ws7G26Rh0qcF1eO3MsAIeB1?e=nH0sOm

[balreenBDO]

Thanks @Ahmedx , I tried this using my original data. It returns everything as 0. Would you happen to know the cause for it?

[Ahmedx]

I don't know, maybe it's because of the connection in the model, or the filter action. need to see your file

[balreenBDO]

Thanks @Ahmedx, Updated file to show my error: Testing.pbix

[Ahmedx]

what dates do you want to exclude here, I don’t understand something, this measure gives empty

[balreenBDO]

The measure should calculate the year-to-date sum from the selected financial year up to the selected month from that financial year. 

E.g. Slicer selection is 2023 and August. Then, it should return July-22 and Aug-22 sum. Hope that makes sense. 

[Ahmedx]

"E.g. Slicer selection is 2023 and August. Then, it should return July-22 and Aug-22 sum. Hope that makes sense."

it's only two months 😞

[Ahmedx]

you didn't do it right
think again and write logic
you get in a slicer for example 2022.11
convert it to a date then write greater than or equal to this date and at the same time less than or equal to this date

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[balreenBDO]

Thanks @Ashish_Mathur , I have added a sample result and further context. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[balreenBDO]

Hi, please find the sample file here: Sample File _Testing.pbix