How to get the accumulated balance from an earlier period?

[Anonymous]

Hello,

In Power BI, I have calculated the accumulated balance from the start. The calendar used is seasonal.

The formula for calculating the accumulated balance is as follows:

Saldo acumulado =
CALCULATE(
'ACCOUNT_INFORMATION'[ACTIVO];
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Calendar';
'Calendar'[NumTemporada];
'Calendar'[Temporada]
);
ALLSELECTED('Calendar')
);
ISONORAFTER(
'Calendar'[NumTemporada]; MAX('Calendar'[NumTemporada]); DESC;
'Calendar'[Temporada]; MAX('Calendar'[Temporada]); DESC
)
)
)

Subsequently, I want to calculate the same balance, but for the previous season. For this I have used the following formula:

Saldo acumulado periodo anterior = IF(HASONEVALUE('Calendar'[NumTemporada]);
CALCULATE(ACCOUNT_INFORMATION[Saldo acumulado];
ALL('Calendar');
FILTER(ALL('Calendar');
'Calendar'[NumTemporada]=VALUES('Calendar'[NumTemporada])-1
)
);
BLANK()
)

The result obtained is the undesired as it only returns the balance of the previous season, but not the accumulated until the previous year.

 Thank you very much and best regards.

[sdjensen]

Hi @Anonymous,

In a Tabular model, that I created, I did something similar. I have a calculated measure called 'Stock Value' this sum all the value until the date filtered either by a slicer or by the filter context in a table.

The dax expression is like this:

IF(
	HASONEVALUE( 'Date'[Fiscal Year] ),
	CALCULATE(
		[Stock Value],
		FILTER(
			ALL( 'Date' ),
			'Date'[FiscalYearNumber] = MAX( 'Date'[FiscalYearNumber] ) -1
				&& 'Date'[Date] <= MAX( 'Date'[PYDate] )
		)
	)
)

PYDate is a column in my date table that for each row return the same date last year. I calculated this column in my SQL query, but you can also calculate this column with a DAX expression.

[Anonymous]

Hi @sdjensen,

How can you create the column PYDate?

Thank you very much and best regards

[sdjensen · Accepted solution]

Hi @Anonymous,

You could try something like this for PYDate:

PYDate = DATEADD('Date'[Date], -1, YEAR)

I will try to explain a little about the data in the columns that I use the formula. Basically this will work if you have a reporting period that differs from a normal calendar year. In my case I have a fiscal calendar that begins July 1st and ends June 30th.

Fiscal Year is in my case Text column having my name for the year:

All dates between July 1st 2008 and June 30th 2009 the value of the column is 2008/2009

All dates between July 1st 2009 and June 30th 2010 the value of the column is 2009/2010

and so on...

FiscalYearNumber holds an integer value for each 'year' (this column is also used to sort the Fiscal Year column and is hidden to the end user). Because this value is an integer I can use it in the dax expression like this  MAX( 'Date'[FiscalYearNumber] ) -1

All dates between July 1st 2008 and June 30th 2009 the value of the column is 2008

All dates between July 1st 2009 and June 30th 2010 the value of the column is 2009

and so on...

Could you perhaps try to post the dax expression that you used? I trust that I don't need to say that you have to replace the table and column names in the expression to macth your data?

[Anonymous]

Hi @sdjensen,

I have used the Edate function for creating PYDate.

I will check what you have proposed and I will tell you.

Thank you very much and best regards.

[v-yulgu-msft]

Hi @Anonymous,

Have you worked it out? If so, please kindly mark the corresponding reply as an answer so that some other users having similar requirement can find the solution more easily. Thanks for your understanding.

Regards,
Yuliana Gu

[Anonymous]

Hi @sdjensen,

I tried the formula but it does not work for me. I guess it will be by setting up my calendar tables.

Thank you very much and best regards.