Forum Discussion

shaunwilks's avatar
shaunwilks
Helper V
8 years ago
Solved

Cumulative/Running Balance in a Matrix

Hello All,   Would appreciate any assistance on this if possible.   I would like to know who to write a DAX measure that gets the last of the cumulative figures for a period, but then totals all ...
Need Help
  • shaunwilks's avatar
    shaunwilks
    8 years ago

    Thanks for that - I have never used SUMMARIZE before so will do some work and investigation into it.

     

    I think I found a wokraround for my problem in the header.

     

    I added an index to the table thats sorted by date.

    I then added a column that retrieved the MAX Index for rows in the table with the the same period (ie month).

    That means I have the index number of the very last record in the month for the fact table and primary key.

     

    Another column is a simple IF statement that sets the value of the field to the running balance value IF the index of that record is the last index id of rows in the same period else set 0.00.

     

    I therefore end with the running balance, set only in the last record on the month.

    When its dropped into a matrix it sums up perfectly.

    I think I may now do the same for the QTR, and use your ISFILTERED Suggestion to show the QTR value if filtered by qtr and month if filtered by month.

     

    Thanks for all your assistance,

shaunwilks's avatar
shaunwilks
Helper V
8 years ago

I have that sample from antoher thread you were happy to try and assist me with.

I am still batlling with both unfortunately.

 

In this case I have no doubt that the ISFILTERED function is the one I need to use.

The problem I have is what measures I put in the ValueIfTrue and ValueIfFalse.

 

I have a measure that shows correctly when the measure is drilled down.

So I would imagine thats what you refer to as the RunningTotalMeas below.

 

The second measure is the battle. I want to sum the RunningTotalMeas itself.

 

So the lines are to show the Last value in that time period and the Header is to show the sum of the lines.

 

Whatever I do I end up getting the Last value both at the line level and the header level of the group.

 

 

Seward12533's avatar
Seward12533
Solution Sage
8 years ago
You would use SUMMARIZE to build a virtual table with the running total column and then sum that.

I don’t have an example handy for that. But try and it you get stuck post a reply or new question.
  • shaunwilks's avatar
    shaunwilks
    Helper V
    8 years ago

    Thanks for that - I have never used SUMMARIZE before so will do some work and investigation into it.

     

    I think I found a wokraround for my problem in the header.

     

    I added an index to the table thats sorted by date.

    I then added a column that retrieved the MAX Index for rows in the table with the the same period (ie month).

    That means I have the index number of the very last record in the month for the fact table and primary key.

     

    Another column is a simple IF statement that sets the value of the field to the running balance value IF the index of that record is the last index id of rows in the same period else set 0.00.

     

    I therefore end with the running balance, set only in the last record on the month.

    When its dropped into a matrix it sums up perfectly.

    I think I may now do the same for the QTR, and use your ISFILTERED Suggestion to show the QTR value if filtered by qtr and month if filtered by month.

     

    Thanks for all your assistance,