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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.