Acumulate value aggrupate

Question

Hi all!I am trying to acumulate hte value of a sum but I need to agregate this value with two columns.&nbsp;The firt field is a combination between Month and Year, and the second one is a concep.&nbsp;I want to sum the Margen for example of "Comida" so in "diciembre - 2017 the Acumulado should be 7.54. In the picture you can see that the value of Acumulado is the sum of all Margen in diciembre - 2017 instead of the Margen for Comida.&nbsp;I have tried&nbsp;Acumulado = CALCULATE(SUMX('Años', [Margen]),FILTER(ALL('Años'), 'Años'[Índice] &lt;= MAX('Año Agregado'[Índice])))&nbsp;where Años is the principal table and Años Agregados is a table where I only have the distinct Años Mes and Concepts. In both table I have created and Index for keep both tables ordenated.&nbsp;&nbsp;Thank you for your help.

amitchandak · Answer

cgil , Not very clear
You need MTD, YTD or cumulative
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] &lt;=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] &lt;=max(Sales[Sales Date])))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

&nbsp;
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer : https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions https://www.archerpoint.com/blog/Posts/creating-date-table-power-bihttps://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184Appreciate your Kudos.

cgil · Answer

The cumulative value works perfect month by month. I have used the first formula you tell me but the problem is when I need this cumulative value not only for month.

If I select one value of the field "Concepto" for example "Casa" it shows the cumulative value for all the values of Concepto not for the value of Casa that I have selected.

Thank you so much for your answer.

v-xicai · Answer

Hi cgil&nbsp;,
&nbsp;
You may change your measure formula like DAX below.
Acumulado =
CALCULATE (
    SUMX ( 'Años', [Margen] ),
    FILTER (
        ALLEXCEPT ( 'Años', 'Años'[Concepto] ),
        'Años'[Índice] &lt;= MAX ( 'Año Agregado'[Índice] )
    )
)

&nbsp;
Best Regards,
Amy&nbsp;
&nbsp;
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

cgil · Answer

Thanks for your answer.&nbsp;It continue shows the same value without agrupate by Concept...

v-xicai · Answer

Hi cgil&nbsp;,
&nbsp;
Try this.
&nbsp;
Acumulado =
CALCULATE (
    SUMX (
        FILTER ( 'Años', 'Años'[Concepto] = MAX ( 'Años'[Concepto] ) ),
        [Margen]
    )
)

Best Regards,
Amy&nbsp;
&nbsp;
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Acumulate value aggrupate

5 Replies

Related content

Column Aggrupation

Acumulative Percentaje

Acumulated value

Acumulate fortnight

Show acumulated values