Previous Total

[itchyeyeballs]

Hi all,

I'm trying to calculate a figure based on a previous total, I need to decrement two columns in order to calculate but having a few issues.

My data and required output looks like

I have tried the following but cant get MeasureX to calculate correctly. MeasureX just needs to show the total for the previous year and previous level i.e. 2015 level 2 = 2014 level 1. Both coumns are whole numbers.

The forula I have tried are:

=calculate(sum(table([value]),table[Year]-1,table[level]-1)

=calculate(
	sum(table[value]),
	filter(all(table[Year]),table[Year]-1),
	filter(all(table[Level),table[level]-1)
	)

Any ideas? I thought this would be straight forward 😞

Thanks

[tringuyenminh92]

Hi @itchyeyeballs,

I have an idea with trick based on time-pattern as below:

• Create calculated column to have unique column from Year and Level 

Level&Year = RawData[Level] * 10000 + RawData[Year]

• Create Calcualted Table for Level&Year column with distinct values 

Lv&Y = DISTINCT(RawData[Level&Year])

Level = DIVIDE( 'Lv&Y'[Level&Year],10000)

year = MOD('Lv&Y'[Level&Year],10000)

(i extract level and year columns for display purpose only)

• Create calculated measure for Previous Total:

prev = CALCULATE(SUM(RawData[Value]),FILTER(all('Lv&Y'),'Lv&Y'[Level&Year] = MAX('Lv&Y'[prev - lv&y])  ))

Please check my sample data and sample pbix file for more details. Hope this works for your case.

I'd like to know the formula for prev of year=2014 and level=1

[itchyeyeballs · Accepted solution]

Hi @tringuyenminh92

Thank you for the help, I wasn't able to follow your complete solution but used some of the core parts to resolve.

As you suggested I merged the two columns I wanted to filter on into a single column, I did this in the initial query

= Table.AddColumn(#"Renamed Columns", "Year_level", each [Year_]*100+[level])

I then created a function to dynamically work out the value (this included an extra filter in the calculate)

=VAR
	yearx = max(table[level])
RETURN
	Calculate(
		sum(table[value]),
		filter(all(table[level],table[Year]),table[year_level]= yearx-101),
		table[another_col]="END"
	)

I then created another measure to ensure the overall total was calulcated correctly

=if(HASONEVALUE(table[Year]),
[Measure],
sumx(values(Table[Year]),[Measure])
)

Which seems to work.

Thank you for the help, was invaluable

[tringuyenminh92]

Hi @itchyeyeballs,

In Vietnamese, invaluable means Vô Giá = Non-valuable =)) just kidding, it's good to know that there is solution for your case.