cancel
Showing results for
Did you mean:
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].

 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
12 REPLIES 12
Solution Sage

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

Frequent Visitor

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

Solution Sage

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

Frequent Visitor

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

Solution Sage

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

Frequent Visitor

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.

Solution Sage

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

Solution Sage

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

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors