topic Re: TOTALYTD accumulating when it's not supposed to in DAX Commands and Tips

TOTALYTD accumulating when it's not supposed to

Anonymous — Mon, 02 Jan 2023 11:17:52 GMT

Hello, I'm using the TOTALYTD quick measure to calculate the year-to-date cummulative value of my MTBF metric but when i have no MTBF values, I was expecting the MTBF (h) YTD stay the same, but instead it's showing me increasing values and I don't know why or where they come from, any clue?

Here is the measure:

MTBF (h) YTD = TOTALYTD([MTBF (h)],'Calendar'[Date])

Here's what's happening for my 'Calendar' [Date] selection

Re: TOTALYTD accumulating when it's not supposed to

Migasuke — Mon, 02 Jan 2023 13:46:07 GMT

Hi Anonymous ,

I think the cause might be done by the expression in the other measure yoou have

[MTBF (h)]

I used the same example with simple sum and everything works well:

Re: TOTALYTD accumulating when it's not supposed to

Anonymous — Mon, 02 Jan 2023 14:12:10 GMT

I see, but in the example you have you're using the sum of values from a column, and I'm using a measure as the <expression> for TOTALYTD.

Re: TOTALYTD accumulating when it's not supposed to

Migasuke — Mon, 02 Jan 2023 14:24:03 GMT

Even If I switch my simple SUM with a measure (containing SUM), result is the same:

Re: TOTALYTD accumulating when it's not supposed to

Anonymous — Mon, 02 Jan 2023 14:38:58 GMT

I don't know what I'm doing wrong then... My "MTBF(h)" is just a divide with a subtraction :

DIVIDE( [#M1] - [#M2] - [#M3] - [#M4], [#M5] )

Re: TOTALYTD accumulating when it's not supposed to

Migasuke — Mon, 02 Jan 2023 15:11:22 GMT

How do you calculate M1, M2...?

Example you have above should not make any problems.

Re: TOTALYTD accumulating when it's not supposed to

Anonymous — Mon, 02 Jan 2023 15:21:55 GMT

They're all calculated in a similar way:

M1 = SUM(Table1[ColumnA])

The rest are as like:

# M2,M3,M4.. = CALCULATE( SUM(Table2[ColumnB]), 'Table3'[ColumnC] in {'x','y','z'} )

Re: TOTALYTD accumulating when it's not supposed to

Migasuke — Mon, 02 Jan 2023 17:56:54 GMT

Frankly only thing which I have in mind is , that one of the "M" measures contains values. That would explain why you see some changes.
Otherwise try to debug the DEVIDE step by step.

Re: TOTALYTD accumulating when it's not supposed to

v-yueyunzh-msft — Tue, 03 Jan 2023 02:38:20 GMT

Hi , Anonymous

I test the DIVIDE() function, in my side , when i use the TOTALYTD() function, it return the same as yours.

For this , you can try to use this method:
(1)This is my test data:

(2)I create a measure as [MTBF(h)] like you:

MTBF (h) = DIVIDE( SUM('Table'[Column2]) - SUM('Table'[Column1]) , SUM('Table'[Column3]))

(3)We can create a measure like this to get the right YTD value:

test = var _cur_date = MAX('Calendar'[Date]) var _t = SUMMARIZE(ALL('Calendar') , 'Calendar'[Date] , "MTBF (h)", [MTBF (h)]) var _t2 = FILTER( _t , [Date]<=_cur_date && YEAR([Date]) = YEAR(_cur_date)) return SUMX(_t2,[MTBF (h)])

(4)Then we can put the fields we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: TOTALYTD accumulating when it's not supposed to

Anonymous — Tue, 03 Jan 2023 10:18:33 GMT

Thank you so much for your help, but does that mean the TOTALYTD does not work if I use the DIVIDE()? I have another measure similar to MTBF that usess DIVIDE and the TOTALYTD there is working as I expected.

Re: TOTALYTD accumulating when it's not supposed to

v-yueyunzh-msft — Wed, 04 Jan 2023 01:16:21 GMT

Hi , Anonymous

Oh, sorry for not detailed explanation. Not for all the DIVIDE() function is not support for the TOTALYTD() function, just when the denominator is not dynamic. If the denominator is static , it works good,like this:

test2 = TOTALYTD([MTBF (h)],'Calendar'[Date])

For this reason, the actual logic is like this:

CALCULATE(DIVIDE( SUM('Table'[Column2]) - SUM('Table'[Column1]) , 2) , DATESYTD('Date'[Date]) )

If the denominators are different, then in different filtering contexts, the sum will not be performed but the average will be performed.

And the bottom layer of TOTAL YTD() is also implemented using the FILTER () function, so it is recommended that you use the above method for some special cases.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly