cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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?

Super User

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

Super User

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.

Super User

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

Super User

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