balance of end of previous year

[RMV]

Hi, 

I'm trying to create a line graph to show 2 values, with month as axis

1. accumulated value per month in current year

2. accumulated value from the first date of transaction (could be more than a year ago) until the end of previous year to be shown constant to all month in the graph

Note: these 2 values must be able to be filtered by month & other categories.

I have a difficulty in applying a correct formula for value #2 that should not be affected by the month filter

I cannot use ALL function, considering the Note above.

anyone can help me what formula I should use?

below is the graph I'm looking for.

Really appreciate help.

[Ashish_Mathur]

Hi @RMV,

Share the link from where i can download your file.

[RMV]

Hi @Anonymous, I need the previous year figure not to be affected by the month filter applied. Only the current year figure is affected by the month filter.

@Ashish_Mathur, sorry I cannot share my pbix here.

I provide some data samples below, though.

The raw data sample is as follow

Entity	Date	Category 1	Category 2	Amount
1	30-Apr-15	A1	B1	1000
1	4-Jun-15	A1	B2	200
2	25-Sep-15	A1	B1	57
2	8-Dec-15	A2	B1	60
1	3-Feb-16	A1	B2	500
1	9-Aug-16	A2	B1	700
2	31-Oct-16	A1	B1	45
2	4-Nov-16	A1	B2	70
1	11-Jan-17	A2	B1	1200
1	22-Feb-17	A1	B1	1300

And the result expected, with only month filter applied is as follow

Entity        All                  Category 1      All

Month      Jan-Feb          Category 2      All

	Current Year	Previous Year
Jan	3832	2632
Feb	5132	2632

Where, Jan previous year is calculated from all 2016 values and before (1000 + 200 + 57 + 60 + 500 + 700 + 45 + 70)

Feb previous year is calculated the same as Jan previous year values

Jan current year is calculated from Jan 2017 values and before (1000 + 200 + 57 + 60 + 500 + 700 + 45 + 70 + 1200)

Feb current year is calculated from Feb 2017 values and before (1000 + 200 + 57 + 60 + 500 + 700 + 45 + 70 + 1200 + 1300) 

When some other filters applied, the result expected is as follow

Entity        1                  Category 1      All

Month      Jan               Category 2      B1

	Current Year	Previous Year
Jan	2200	1700
Feb		1700

Jan Previous Year is calculated with 2016 values and before for Entity 1 & Category 2 = B1 (1000 + 700)

Feb Previous Year is calculated the same as Jan Previous Year

Jan Current Year is calculated with Jan 2017 values and before for Entity 1 & Category 2 = B1 (1000 + 700 + 1200)

Feb Current Year is showing blank, since the month is filtered to Jan only

[Ashish_Mathur · Accepted solution]

Hi @RMV,

See if my solution here helps.

[RMV]

Hi @Ashish_Mathur

Wonderful! It works.

Thanks a lot for the help.

Regards,

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[Anonymous]

Try this:

Cumulative For Last Year = 
VAR StartingMonth = MIN(Calendar[Month]) 
VAR EndingMonth = MAX(Calendar[Month])
VAR CurrentYear = MAX(Calendar[Year])
VAR LastYear = CurrentYear - 1
RETURN
CALCULATE(
	[Sum Measure],
	Filter(
		ALL(Calendar),
		Calendar[Year] = LastYear
		  && Calendar[Month] >= StartingMonth
		  && Calendar[Month] <= EndingMonth
	)
)

This will listen to the month slicer and return all months within the the selected range, but only for last year.