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

New Member

Accumulate Measure Calculation to Current Month

I'm new to Power BI and I'd be very glad if you guys could help me solve the following problem.

I have to show the following data in a Matrix, but for it to make sense every row context have to be evaluated differently.

I've come up with this formula, but I have no idea how to make it work for the "Pendente" row.

``````MatrixRowMeasure =
VAR _Dummy = 0

RETURN
// Here, I'm using the inactivate date relationship and I just want the "Projeto" that are "Cancelado" or "Concluído"
CALCULATE(COUNTROWS('Tabela'), USERELATIONSHIP(Data[Date], 'Tabela'[Data Finalização]), 'Tabela'[Motivo] = "Projeto"),

// For "Projeto", just count rows using the active date relationship, which is 'Tabela'[Data de Criação]
CALCULATE(COUNTROWS('Tabela')),

// The measure I need to make which will accumulate the calculations above from both IF paths with those same calculations but from previous months
_Dummy
)
)``````

Basically, for the _Dummy part (the "Pendente" row) to make sense, it would have to get an initial value coming from another Measure and sum it with a calculation for the current context and I can get it to work for the first context of "Janeiro", but I also need the subsequent months to get the cumulative values of "Pendente" until that current month. For example, in the context of the month of "Fevereiro", the measure would return the calculation for the current month plus the result from the calculation of the month of "Janeiro", and so on for the following months.

Here is the expected result:

Indicadores Janeiro Fevereiro Março Abril Maio
 Cancelado 5 3 2 0 8 Concluído 4 0 1 0 2 Pendente 7 7 13 13 15 Projeto 10 3 9 0 12

And the calculation for the first value of "Pendente" is

And then uses the value from the previous month of "Pendente" for the next calculations.

2 REPLIES 2
Community Champion

@Linkk Is your data laid out exactly like shown in the matrix and in Excel?  If so, you're doing it wrong.

Power Query unpivot your months into columns and make them actual date values.  Any date in a month will work.  second, date table!  Google the importance of this!

You do those two things and this should be fairly easy with a basic SUM() with category down the side and Month Name (from the date table) across the top.

New Member

Thanks for you attention @littlemojopuppy! I'm actually using a data table, see:

and the matrix is laid out the way you mentioned, but I didn't mention 'Tabela 2'[Indicadores] is a Table with distinct values of 'Tabela 1'[Indicadores], that's why i'm counting the rows in 'Tabela 1':

I'm using a slicer for the selection of the year, which is considering just the year of 2023 for the images above.

Although I've seen some posts tackling cumulative measure, I wasn't able to translate them for my task at hand.

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.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors