Forum Discussion

angelocecinati's avatar
angelocecinati
Frequent Visitor
8 years ago
Solved

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 aggregat...
Need Help
Tips and Tricks
  • angelocecinati's avatar
    angelocecinati
    8 years ago

    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!

     

     

     

     

     

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi angelocecinati,

 

Please try this:

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

 

Best regards,

Yuliana Gu

angelocecinati's avatar
angelocecinati
Frequent Visitor
8 years ago

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!