Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
balreenBDO
Frequent Visitor

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
12 REPLIES 12
Ahmedx
Super User
Super User

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
Screen Capture #941.png

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

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

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

what dates do you want to exclude here, I don’t understand something, this measure gives empty
Screen Capture #944.png

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. 

"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 😞

 

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.