Subtotals YTD not calculating correct from DAX

[pelusch]

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

))

2)

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

 
2)

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

[pelusch]

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

[BeaBF]

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

[pelusch]

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

[BeaBF]

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

[pelusch]

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]

)

[BeaBF]

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

[pelusch]

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. 

[BeaBF]

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

[pelusch]

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:

[BeaBF]

@pelusch write me the expected output for filter on 2.

BBF

[pelusch]

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

[BeaBF]

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

[pelusch]

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

[BeaBF · Accepted solution]

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

[pelusch]

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

[BeaBF]

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

[pelusch]

My bad, I only imported the top 1000 rows from Date table by mistake. I tried to change the filter with Query1 fields - but is this possible? 

The filter then looks like this: 

            CALCULATETABLE(

                NAV,

                FILTER(

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

                    Query1[Datum] = SelectedYear && Query1[Datum] <= SelectedMonth &&

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

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

                )

It's pretty clear that I'm lost here. Is it possible to write the filter like this when I have to combine two tables? You say that I have to change filters with Query1 fields, but I guess I still have to use the NAV-table for the category codes. 

I also tried to write my filter like this, and this did not work: 

            CALCULATETABLE(

                NAV,

                FILTER(

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

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

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

                ),

                FILTER(ALL(Query1),

                Query1[Date] = SelectedYear && Query1[Date] <= SelectedMonth

                ))


I mailed you a updates pbix-file. 

[BeaBF]

@pelusch i'm sending you the last pbix. I've fixed the amount_ytd measure and the last one. I think it's working but let me know if it is correct.

BBF

[pelusch]

@BeaBF Now It's working perfect, many thanks for helping me. Appreciates it! 🙂 

[pelusch]

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]

)