Running totals filling in blanks with previous month

[nathannn]

I have the following table showing the running total of a balance for each period (month)

The sum of the running total has been done within the query, however we have some fields that have no value. 

This is affecting the grand totals of the cumulative table, how am I able to configure the visual to display the previous total if no value is populated for that month?  

Thanks.

[v-rzhou-msft · Accepted solution]

Hi @nathannn 

You can use sumx to build a cumlative total measure.

I build a sample table to have a test.

Your values may be a measure:

M.Value = SUM('Table'[Value])

Cumlative total measure:

Measure = 
Var _Cumlativetotal = SUMX(FILTER(ALL('Table'),'Table'[NomianlGroupType]=MAX('Table'[NomianlGroupType])&&'Table'[Period]=MAX('Table'[Period])&&'Table'[Category]<=MAX('Table'[Category])),[M.Value])
Var _Value = IF(ISBLANK(_Cumlativetotal),0,_Cumlativetotal)
Return
IF(HASONEVALUE('Table'[NomianlGroupType]),_Value,SUMX(FILTER('Table','Table'[Period]=MAX('Table'[Period])),[M.Value]))

Result:

Left visual is a normal sum visual like yours and the right one is a running total visual by GroupType and Category.

You can download the pbix file from this link: Running totals filling in blanks with previous month

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Fowmy]

@nathannn 

Can you share the measure in the calculation to check?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[nathannn]

@Fowmy Thanks for your response! 

The measure is just a simple sum formatted to put () round the negative values. 

The running total has been done in SQL. Is there a better alternative within PBI?

[Fowmy]

@nathannn 

To create financial statements in Power BI and to make use of all the amazing functionalities, you should bring in only the GL amount and so all other calculations within POWER BI. 

Your running balance will not respond to slicers and filtering in many cases as it from the source data. 

Search on YouTube for Power BI Financial reports, you will get good videos to follow. 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn