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

## Subtotal YTD in Matrix

Hello,

I've created a YTD measure in a matrix and I'd like to show a subtotal summing years by category. The problem I'm facing is that the subtotal shows just the last year and does not aggregate the years.

Anyone has ideas about how to fix it?

Angelo

1 ACCEPTED SOLUTION
Frequent Visitor

The TOTALYTD function works in the year context only. Thus I created a custom mesaure has suggested in this post:

https://community.powerbi.com/t5/Desktop/Cumulative-Running-Totals-in-DAX/m-p/42608

Cumulative Value =
IF(
MIN(Dim_Calendar[Date])<=CALCULATE(
MAX(Dim_Calendar[Date]);
ALL(TF));
CALCULATE([Valore_M];
FILTER(All(Dim_Calendar[Month]);Dim_Calendar[Month]<=MAX((Dim_Calendar[Month]
))));BLANK())

Now it works!

3 REPLIES 3
Microsoft Employee

```ValueYTD =
SUMX ( sales_table, TOTALYTD ( [Value_M], 'Dim_Calendar'[Date] ) )```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

The TOTALYTD function works in the year context only. Thus I created a custom mesaure has suggested in this post:

https://community.powerbi.com/t5/Desktop/Cumulative-Running-Totals-in-DAX/m-p/42608

Cumulative Value =
IF(
MIN(Dim_Calendar[Date])<=CALCULATE(
MAX(Dim_Calendar[Date]);
ALL(TF));
CALCULATE([Valore_M];
FILTER(All(Dim_Calendar[Month]);Dim_Calendar[Month]<=MAX((Dim_Calendar[Month]
))));BLANK())

Now it works!

Frequent Visitor

Hi Yuliana,

Thanks for your reply. This solution makes the subtotal works properly. Unfortunatly the YTD measure does not work over the months, cause it should sum the passed period values. .

I think that if I put the sumx function in front of the measure it computes the YTD for the single row of the fact table.

If I change the order of the matrix it works!

Do I have to change the model behind?

Thanks again,

Angelo

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.