cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

CALCULATE(
'ACCOUNT_INFORMATION'[ACTIVO];
FILTER(
CALCULATETABLE(
SUMMARIZE(
'Calendar';
);
ALLSELECTED('Calendar')
);
ISONORAFTER(
)
)
)

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

ALL('Calendar');
FILTER(ALL('Calendar');
)
);
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.

1 ACCEPTED SOLUTION
Solution Sage

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
6 REPLIES 6
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
Helper IV

Hi @sdjensen,

How can you create the column PYDate?

Thank you very much and best regards

Solution Sage

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
Helper IV

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.

Microsoft Employee

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.
Helper IV

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.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.