Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Isidro
Helper IV
Helper IV

How to get the accumulated balance from an earlier period?

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.

Saldo acumulado.jpg

 

 Thank you very much and best regards.

 

 

1 ACCEPTED SOLUTION

Hi @Isidro,

 

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?

/sdjensen

View solution in original post

6 REPLIES 6
sdjensen
Solution Sage
Solution Sage

Hi @Isidro,

 

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.

/sdjensen

Hi @sdjensen,

 

How can you create the column PYDate?

 

Thank you very much and best regards

Hi @Isidro,

 

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?

/sdjensen

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.

Hi @Isidro,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors