DAX Measure for opening balance

[Anonymous]

Hello all,

I have this measure which calculates the closing balance per filtered period:

RestBudgetAcrossYears =

var Planned_budget = sum(DIA_PBI_SAP_ZEITPROJECTAUFGABEN[projectitembudgettime])

var maxSelectedDate = Max('Date'[Date])

var AllActualWorkDoneBeforeCurrentDate =

CALCULATE(

SUM(DIA_PBI_SAP_ZEITPROJECTLEISTUNGAUSSAP[Verrechenbar]),

FILTER(ALL('Date'[Date]), [Date] <= maxSelectedDate)

)

return

if ([Rapportierte Zeit] <> BLANK(),Planned_budget-AllActualWorkDoneBeforeCurrentDate)

I would like to adjust the measure so it gives me the opening balance for each period.

This is what the result should look like (the measure above gives RestBudgetAcrossYears, but I want a measure to calculate OpeningBudgetAcrossYears):

Jahr	Monat	OpeningBudgetAcrossYears	Rapportierte Zeit	Verrechenbar	RestBudgetAcrossYears
2017	Jan	187403.1205	10538.99631	1486.580817	185916.5397
2017	Feb	185916.5397	9452.016057	1541.166325	184375.3734
2017	Mar	184375.3734	11194.53299	1455.316661	182920.0567
2017	Apr	182920.0567	8475.131983	1081.632323	181838.4244
2017	May	181838.4244	10471.29832	1466.866548	180371.5578
2017	Jun	180371.5578	9868.032323	1369.116319	179002.4415
2017	Jul	179002.4415	8198.33665	1050.43932	177952.0022
2017	Aug	177952.0022	9148.849335	1222.983268	176729.0189
2017	Sep	176729.0189	9680.216311	1626.130313	175102.8886
2017	Oct	175102.8886	9516.42295	1812.091642	173290.797
2017	Nov	173290.797	11581.96562	1969.362323	171321.4347
2017	Dec	171321.4347	8226.330984	1447.260661	169874.174
2018	Jan	169874.174	169874.174	1553.776327	168320.3977
2018	Feb	168320.3977	9490.483305	1483.083325	166837.3143

Could someone kindly assist with this?

Many thanks.

[v-yangliu-msft]

Hi    @Anonymous  ,

Here are the steps you can follow：

1. You can define this data to form a variable.

2. Create calculated column.

OpeningBudgetAcrossYears_column =

var _ definition = CALCULATE(MAX('Table'[RestBudgetAcrossYears]),FILTER('Table','Table'[Index]=1))

var _1=CALCULATE(SUM('Table'[RestBudgetAcrossYears]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))

return IF([Index]=1,_ definition -0,_1)

3. Result：

Best Regards,

Liu Yang

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

[Anonymous]

Hi Liu,

I'm afraid that the Index part of your suggested solution doesn't work.

I've created the index column based on a date column. However, when I refresh the query from the source, and new lines are populated with the same date as the existing lines, then the indexing is messed up, and is no longer in order.

Do you have another solution?

Many thanks.

[v-yangliu-msft]

Hi  @Anonymous ,

For index confusion, you can do this:

1. You can create a calculated column to connect [Jahr] and [Monat]

Month & Year = 'Table'[Jahr]&"-"&'Table'[Monat]

Result：

2. Use Column tools to convert the new column Month & Year to date

3. Create calculated column and use rankx function to build index.

index =
RANKX('Table','Table'[Month & Year],,ASC)

4. Create measure.

OpeningBudgetAcrossYears_column =
var _0= CALCULATE(MAX('Table'[RestBudgetAcrossYears]),FILTER('Table','Table'[index]=1))
var _1=CALCULATE(SUM('Table'[RestBudgetAcrossYears]),FILTER('Table','Table'[index]=EARLIER('Table'[index])-1))
return IF([index]=1,_0-0,_1)

Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi    @Anonymous ,

It won't change，the index column continues

You can do this, create measure.

OpeningBudgetAcrossYears_measure = var _1=CALCULATE([RestBudgetAcrossYears],FILTER('Table','Table'[Index]=max('Table'[Index])-1)) return IF([Index]=1,187403.12-0,_1)

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

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

[Anonymous]

Hi Liu

The very first opening balance is not always 187403.12. How can I please change the measure to reflect this?

Thanks.

[v-yangliu-msft]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Enter power query through transform data and select add column --- index column --- from 1 to generate the index

2. Create calculated column.

OpeningBudgetAcrossYears_column =
var _1=CALCULATE(SUM('Table'[RestBudgetAcrossYears]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
return IF([Index]=1,187403.12-0,_1)

3. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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

[Anonymous]

Hi Liu,

Thanks for your response.

A couple of a questions:

- The 'RestBudgetAcrossYears' values are actually created through a measure, and do not exist in the table/query. So, the 187403.12 is not fixed. How could I change the DAX measure in order to reflect this?

- I've sorted my data in the table based on the date column ascending (the real table has entries based on dates), and then created the index column. What will happen when the data is refreshed in the future, and more entries are added to the existing dates in the table?

Looking forward to hearing back from you.

Best wishes.

[Anonymous]

Hi @amitchandak 

the column 'OpeningBudgetAcrossYears' is missing. I created the example above in Excel.

So, it should bring back:

first row: Planned_budget minus AllActualWorkDoneBeforeCurrentDate = 187403.12 - 0 = 187403.12

second row: RestBudgetAcrossYears for the previous period = 185916.53

third row: RestBudgetAcrossYears for the previous period = 184375.37

and so on...

If you have another way of achieving the same results that'd also be great.

Thanks.

[amitchandak]

@Anonymous , Not very clear with you data, what is missing and what you want to get. Usually we have rebuild opening budget with help first openining budget and cumulative +/- of in and out amount