Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Linkk
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.

Linkk_0-1698025513662.png

 

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 
IF (SELECTEDVALUE('Tabela 2'[Indicadores]) IN { "Cancelado", "Concluído" },
    // 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"),

    IF( SELECTEDVALUE('Tabela 2'[Indicadores]) = "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
Cancelado53208
Concluído40102
Pendente77131315
Projeto1039012

 

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

Linkk_1-1698027141101.png

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

2 REPLIES 2
littlemojopuppy
Community Champion
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.

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

Linkk_0-1698060213135.png

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': 

Linkk_1-1698060255459.png

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.