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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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