topic Re: cumul of value in Matrix in DAX Commands and Tips

cumul of value in Matrix

paololito — Wed, 06 Jul 2022 09:58:50 GMT

Hello community,

I'm looking to do a dax calculation in my matrix but unfortunately I dind't find the way to achieve this .

what I have :

what I would like

G/L #	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023
131000	1.100.000	1.100.000	1.100.000	1.150.000	1.300.000	1.300.000	1.400.000	1.500.000	1.800.000	2.100.000	2.100.000	2.100.000

can you please help me on this request ?

Thanks a lot

paololito

Re: cumul of value in Matrix

arichard19 — Wed, 06 Jul 2022 10:09:37 GMT

If you are hoping to calaculate the running cumulative total of your balance sheet from you General Ledgers something like this would be helpful -

Running General Ledger Amount =

CALCULATE( SUM('General Ledger Entries'[Amount]), FILTER( ALL(Calender[Date]), Calender[Date] <= MAX((Calender[Date]))))

You will need a calendar within your data model - https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Re: cumul of value in Matrix

tamerj1 — Wed, 06 Jul 2022 10:08:39 GMT

HI paololito
The year column from which table? What is the name of the column used in the values of the matrix? From which table? G/L# from which table. Any relationships?

Re: cumul of value in Matrix

paololito — Wed, 06 Jul 2022 10:17:36 GMT

Hi tamerj1 , thanks for your fast reply .

The year column from which table? FactEntry[FinYear] is calculated column based on a posting date

What is the name of the column used in the values of the matrix?

Row : G/L # of the Table DimAccount

Column : Fin Year (calculated column) of the Table FactEntry

Value = Amount of the table Factentry

Re: cumul of value in Matrix

paololito — Wed, 06 Jul 2022 10:21:32 GMT

Hi arichard19

your solution is working but the blank need to be filled also ( see screenshot below)

Re: cumul of value in Matrix

paololito — Wed, 06 Jul 2022 13:15:58 GMT

The solution proposed bu arichard19 seems be a good starting point but how I can also fill the blank value ?

Running General Ledger Amount =

CALCULATE( SUM('General Ledger Entries'[Amount]), FILTER( ALL(Calender[Date]), Calender[Date] <= MAX((Calender[Date]))))

does anyone have a solution ?

Thanks a lot for your help !

Re: cumul of value in Matrix

arichard19 — Wed, 06 Jul 2022 13:39:08 GMT

If I recreate the scenario shown above, the DAX measure I suggested fills the blank values as well within my own Power BI files.

You may want to reevaluate the structure of your Calendar Table and the relationships built between your General Ledgers and the Calendar Table.

Also, make sure you have time intelligence active in your file

Re: cumul of value in Matrix

v-easonf-msft — Mon, 11 Jul 2022 09:09:01 GMT

Hi, paololito

You need to create a separate calendar table for the field "Year" on the matrix column, and then create the measure based on the new field "Year" in separate calendar table and the original Row field in the fact table.

Best Regards,
Community Support Team _ Eason

Re: cumul of value in Matrix

paololito — Wed, 10 Aug 2022 14:29:09 GMT

Hello ,

Unfortunatelly, I don't find the way to achieve the solution proposed .

I want to fill the empty cell with the last non-empty value .

Can you please ask again your support on this matter .

All fields related to this matrix are on the same table ( column , row , value)

Thanks in Advance

paololito