topic Re: Subtotals YTD not calculating correct from DAX in DAX Commands and Tips

Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 08:26:29 GMT

Hey guys,

First post in here! I have some trouble with my dax to calculate my subtotals in a cumulative way in a Year-to-date scenario. Basically, it works in the similar Month-to-date scenario when I'm choose single months in my slicer, but not when I'm trying to include multiple months for YTD.

My dax formulas related to the YTD-scenario looks like this:

1)

Amount_YTD =

VAR MaxValdManadNummer = MAX('Date'[Month])

VAR MaxYear = MAX('Date'[Year])

RETURN

CALCULATE(SUM(NAV[Amount]),

FILTER(ALL('Date'),

'Date'[Year] = MaxYear &&

'Date'[Month] <= MaxValdManadNummer

))

ActualsCumulativeTotalYTD =

VAR MaxKategori = MAX(NAV[Account Category Code])

RETURN

IF(

NOT(ISINSCOPE(NAV[Account Subcategory Code])),

CALCULATE(

SUMX(

CALCULATETABLE(

SUMMARIZE(NAV, NAV[Account Category Name], "Utfall", [Amount_YTD]),

FILTER(

ALLSELECTED(NAV),

NAV[Account Category Code] <= MaxKategori

)

[Amount_YTD]

)

[Amount_YTD]

)

By now, my visualization looks like this. As you can see the subtotals are not calculating the numbers in a cumulative way, which I want on the [Account Category Name]-level. This problem do not occur when I'm working with my MTD-scenario.

Slicers that's used in this context: 'Date'[Year], 'Date'[Month]'.

The MTD-scenario (that's working perfectly) looks like this:

1)

Amount_MTD =

CALCULATE(SUM(NAV[Amount]))

ActualsCumulativeTotalMTD =

VAR MaxKategori = MAX(NAV[Account Category Code])

RETURN

IF(

NOT(ISINSCOPE(NAV[Account Subcategory Code])),

CALCULATE(

SUMX(

CALCULATETABLE(

SUMMARIZE(NAV, NAV[Account Category Name], "Utfall", [Amount_MTD]),

FILTER(

ALLSELECTED(NAV),

NAV[Account Category Code] <= MaxKategori

)

[Utfall]

)

[Amount_MTD]

)

Anyone who can help? 🙂

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 08:35:26 GMT

Anonymous Hi! Try with:

ActualsCumulativeTotalYTD =
VAR MaxKategori = MAX(NAV[Account Category Code])
RETURN
IF(
NOT(ISINSCOPE(NAV[Account Subcategory Code])),
CALCULATE(
SUMX(
CALCULATETABLE(
SUMMARIZE(NAV, NAV[Account Category Name], "Utfall", [Amount_YTD]),
FILTER(
ALLSELECTED(NAV),
NAV[Account Category Code] <= MaxKategori
)
),
[Amount_YTD]
),
FILTER(ALLSELECTED(NAV), NAV[Account Category Code] <= MaxKategori)
),
[Amount_YTD]
)

if it's ok accept my answer as solution, instead please provide some sample data on which you calculate the measure and the expected output.

BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 10:40:38 GMT

Hi. Thanks! Unfortunately It's not working in the subtotal and YTD-level, see below:

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 11:36:05 GMT

This it the expected output of the data above when I use slicers 'Date'[Year] = 2024 and 'Date'[Month] = 2.

Account Category Name	Accoubt Subcategory Name	ActualsCumulativeTotalMTD	ActualsCumulativeTotalYTD
Totala Intäkter	Försäljning	500	1100
	Total	500	1 100
Bruttovinst	Varukostnad	-100	-300
	Total	400	800
Driftresultat	Personalkostnad	-350	-750
	Förbrukningskostnader	-100	-250
	Övriga Kostnader	-50	-90
	Total	-100	-290
Total		-100	-290

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 11:25:54 GMT

This is the first part of my data:

Year	Month	Account Category Name	Account Category Code	Account Subcategory Name	Account Subcategory Code	Amount
2024	1	Totala Intäkter	10	Försäljning	100	500
2024	2	Totala Intäkter	10	Försäljning	100	600
2024	1	Bruttovinst	20	Varukostnad	200	-100
2024	2	Bruttovinst	20	Varukostnad	200	-200
2024	1	Driftresultat	30	Personalkostnad	300	-350
2024	1	Driftresultat	30	Förbrukningskostnader	310	-100
2024	1	Driftresultat	30	Övriga Kostnader	320	-50
2024	2	Driftresultat	30	Personalkostnad	300	-400
2024	2	Driftresultat	30	Förbrukningskostnader	310	-150
2024	2	Driftresultat	30	Övriga Kostnader	320	-40

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 12:43:42 GMT

Anonymous Try with:

ActualsCumulativeTotalYTD =

VAR MaxKategori = MAX(NAV[Account Category Code])

VAR MaxSubKat = MAX(NAV[Account Subcategory Code])

RETURN

IF(

NOT(ISINSCOPE(NAV[Account Subcategory Code])),

CALCULATE(

SUMX(

CALCULATETABLE(

NAV,

FILTER(

ALLSELECTED(NAV),

NAV[Account Category Code] <= MaxKategori && NAV[Account Subcategory Code] <= MaxSubKat

)

[Amount_YTD]

)

[Amount_YTD]

)

BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 13:14:48 GMT

When I use this formula it does only consider the filtered month on the [Account Category Name]-level.

It's so strange because then I use [Account Category Code] as a row then the calculations makes it perfect (both the MTD and YTD calculation with cumulative subtotals). But when I use [Account Category Name] it does not work.

I also use exactly the same code:

ActualsCumulativeTotalYTD =

VAR MaxKategori = MAX(NAV[Account Category Code])

RETURN

IF(

NOT(ISINSCOPE(NAV[Account Subcategory Code])),

CALCULATE(

SUMX(

CALCULATETABLE(

SUMMARIZE(NAV, NAV[Account Category Code], "Utfall", [Amount_YTD]),

FILTER(

ALLSELECTED(NAV),

NAV[Account Category Code] <= MaxKategori

)

[Amount_YTD]

)

[Amount_YTD]

)

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 13:37:08 GMT

Anonymous ok, i've done a fix:

ActualsCumulativeTotalYTD_2 =

VAR MaxKategori = MAX(NAV[Account Category Code])

VAR MaxSubKat = MAX(NAV[Account Subcategory Code])

// Use a variable to store the context values to account for names

VAR CurrentCategoryName = MAX(NAV[Account Category Name])

VAR CurrentSubCategoryName = MAX(NAV[Account Subcategory Name])

RETURN

IF(

NOT(ISINSCOPE(NAV[Account Subcategory Name])), // Adjust to check for Subcategory Name

CALCULATE(

SUMX(

CALCULATETABLE(

NAV,

FILTER(

ALLSELECTED(NAV),

(NAV[Account Category Code] <= MaxKategori || NAV[Account Category Name] = CurrentCategoryName) &&

(NAV[Account Subcategory Code] <= MaxSubKat || NAV[Account Subcategory Name] = CurrentSubCategoryName)

)

[Amount_YTD]

)

[Amount_YTD]

)

BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 13:55:17 GMT

Thanks but not working 😞 I have been working for three days to solve this problem and I don't think there is an issue with the data model.

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 14:10:43 GMT

Anonymous For me it's working, in the screen the first measure, wrong, and the second one corresponding to the desided output:

what's wrong?
BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 15:02:41 GMT

Of some reason, I don't get the same output as you so I think I have some big problems here. I have created a table with the testdata and don't manage to get the same figures. If I am only using the 'Table' without any other relationships and picks the [Year] and [Month] as a slicer I get wrong results.

Amount_YTD_2_Table =

VAR MaxValdManadNummer = MAX('Table'[Month]) -- Antag att du har en kolumn 'MonthNumber'

VAR MaxYear = MAX('Table'[Year])

RETURN

CALCULATE(SUM('Table'[Amount]),

FILTER(ALL('Table'),

'Table'[Year] = MaxYear &&

'Table'[Month] <= MaxValdManadNummer

))

ActualsCumulativeTotalYTD_2 =

VAR MaxKategori = MAX('Table'[Account Category Code])

VAR MaxSubKat = MAX('Table'[Account Subcategory Code])

// Use a variable to store the context values to account for names

VAR CurrentCategoryName = MAX('Table'[Account Category Name])

VAR CurrentSubCategoryName = MAX('Table'[Account Subcategory Name])

RETURN

IF(

NOT(ISINSCOPE('Table'[Account Subcategory Name])), // Adjust to check for Subcategory Name

CALCULATE(

SUMX(

CALCULATETABLE(

'Table',

FILTER(

ALLSELECTED('Table'),

('Table'[Account Category Code] <= MaxKategori || 'Table'[Account Category Name] = CurrentCategoryName) &&

('Table'[Account Subcategory Code] <= MaxSubKat || 'Table'[Account Subcategory Name] = CurrentSubCategoryName)

)

[Amount_YTD_2_Table]

)

[Amount_YTD_2_Table]

)

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 15:25:40 GMT

Anonymous No, i've calculated a new table named Date, in this way:

Date = SUMMARIZE(NAV, NAV[Year], NAV[Month]) and then created a Key in both tables (date and NAV) as:

CONCATENATE('Date'[Year], 'Date'[Month])

and related the two tables by this new field KEY.

if you send me your email by pvt message, i can send you my pbix file.

BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 16:42:04 GMT

Now I have looked through your file. From my point of view, the calculation are doing fine when month is not selected. But when I select a month as nr 2 for instance, I'm getting the wrong calculations . In your screenshot it looks like you have selected the filter (?).

This reminds me of my main issue, I note that if I don't select a month, the YTD-calculation are doing just fine, but in this case I want the selected month to be the last month of the YTD, if that makes sense.

See the differences in the screenshots below:

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 17:10:01 GMT

Anonymous write me the expected output for filter on 2.

BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 17:24:08 GMT

This is the expected output for month filter on 2:

Account Category Name	Accoubt Subcategory Name	ActualsCumulativeTotalMTD	ActualsCumulativeTotalYTD
Totala Intäkter	Försäljning	600	1 100
	Total	600	1 100
Bruttovinst	Varukostnad	-200	-300
	Total	400	800
Driftresultat	Personalkostnad	-400	-750
	Förbrukningskostnader	-150	-250
	Övriga Kostnader	-40	-90
	Total	-190	-290
Total		-190	-290

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 17:42:39 GMT

Anonymous here the measure adjusted:

ActualsCumulativeTotalYTD =

VAR MaxKategori = MAX(NAV[Account Category Code])

VAR MaxSubKat = MAX(NAV[Account Subcategory Code])

// Variables for the current category and subcategory names

VAR CurrentCategoryName = MAX(NAV[Account Category Name])

VAR CurrentSubCategoryName = MAX(NAV[Account Subcategory Name])

// Calculate cumulative total YTD by ignoring month filter

RETURN

IF(

NOT(ISINSCOPE(NAV[Account Subcategory Name])), // Adjust for Subcategory Name

CALCULATE(

SUMX(

CALCULATETABLE(

NAV,

FILTER(

ALLSELECTED(NAV),

(NAV[Account Category Code] <= MaxKategori || NAV[Account Category Name] = CurrentCategoryName) &&

(NAV[Account Subcategory Code] <= MaxSubKat || NAV[Account Subcategory Name] = CurrentSubCategoryName)

)

[Amount_YTD]

REMOVEFILTERS('Date'[Month]) // Remove the Month filter but keep Year

[Amount_YTD]

)

Old measure vs new measure with filter:

BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 18:10:14 GMT

Thanks, it works fine when you select month nr 2. But when I select month nr 1 (see below), the calculation are not correct if you're looking at the [Account Category Name] level. Sorry if I didn't made that clear for you.

When you select month nr 1, my expected output are:

Account Category Name	Accoubt Subcategory Name	ActualsCumulativeTotalMTD	ActualsCumulativeTotalYTD
Totala Intäkter	Försäljning	500	500
	Total	500	500
Bruttovinst	Varukostnad	-100	-100
	Total	400	400
Driftresultat	Personalkostnad	-350	-350
	Förbrukningskostnader	-100	-100
	Övriga Kostnader	-50	-50
	Total	-100	-100
Total		-100	-100

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Wed, 25 Sep 2024 18:19:07 GMT

Anonymous try with this new version:

ActualsCumulativeTotalYTD_2 =

VAR SelectedYear = SELECTEDVALUE('Date'[Year])

VAR SelectedMonth = SELECTEDVALUE('Date'[Month])

VAR MaxKategori = MAX(NAV[Account Category Code])

VAR MaxSubKat = MAX(NAV[Account Subcategory Code])

// Variables for the current category and subcategory names

VAR CurrentCategoryName = MAX(NAV[Account Category Name])

VAR CurrentSubCategoryName = MAX(NAV[Account Subcategory Name])

// Check if we're looking at a subcategory level

RETURN

IF(

NOT(ISINSCOPE(NAV[Account Subcategory Name])),

CALCULATE(

SUMX(

CALCULATETABLE(

NAV,

FILTER(

ALL(NAV), // Remove all filters from NAV

NAV[Year] = SelectedYear && NAV[Month] <= SelectedMonth &&

(NAV[Account Category Code] <= MaxKategori || NAV[Account Category Name] = CurrentCategoryName) &&

(NAV[Account Subcategory Code] <= MaxSubKat || NAV[Account Subcategory Name] = CurrentSubCategoryName)

)

[Amount_YTD]

)

[Amount_YTD]

)

no one needs to apologize, we are working together 🙂
BBF

Re: Subtotals YTD not calculating correct from DAX

Anonymous — Wed, 25 Sep 2024 19:38:22 GMT

Hey, you are awesome at this. Great job, It works! The datekey has now been YearMonth but in my datamodel a Date should fit better. I tried to import my original Date table and changed the key in the pbix-file that you sent me and noted that the formula didn't fit, wasn't expecting that :(. Perhaps you can take a look at this? 🙂 I take a chance and mailing the file back to you.

//Per

Re: Subtotals YTD not calculating correct from DAX

BeaBF — Thu, 26 Sep 2024 07:21:42 GMT

Anonymous Hi! You have still my Date table in the filters, without relation to NAV, so they don't work. By changing filters with Query1 fields, they works, but the problem is that max year in Query1 is 2017, and NAV has only 2024 datas. So you have to have the same year both in Query1 and NAV to make it work.

Please accept my last message as a solution, to help other users with formula.

BBF