cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper III

## Formula to get prior month asset balance to the FIRST selection in the month slicer

I have the following DAX:

Average of Total Assets = VAR _MSD =
MAX ( 'Date'[MonthID] )
VAR _YSD =
MAX ( 'Date'[Year] )
VAR _LYED = _YSD - 1
VAR _MED = 12
VAR _A =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = 12 && 'Date'[Year] = _LYED )
)
VAR _B =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER ( ALL ( 'Date' ), 'Date'[MonthID] = _MSD && 'Date'[Year] = _YSD )
)
RETURN
( _A + _B) /2

For variable A, instead of getting the total assets as of december's month end for the previous year to the selection that I made, I would like it to be changed to pulling the total assets as of the prior month end to the FIRST selected month in the slicer.  For example, if I choose April, May, and June of 2023, I like the formula to pull March 31, 2023's total assets instead of pulling December 31, 2022's total assets.  If I choose May and June 2023 in the slicer, the formula should pull April 30, 2023's total asset balance.  Variable B is correct as that will pull June 30, 2023's Total assets.  Please advise.  Thanks.
2 REPLIES 2
Super User

Hello @rwong1,

``````VAR _FirstSelectedMonth =
CALCULATE(MIN('Date'[MonthID]), ALLSELECTED('Date'))

VAR _YearOfFirstSelectedMonth =
CALCULATE(MIN('Date'[Year]), ALLSELECTED('Date'))

VAR _PriorMonth =
IF(_FirstSelectedMonth > 1, _FirstSelectedMonth - 1, 12)

VAR _YearOfPriorMonth =
IF(_FirstSelectedMonth > 1, _YearOfFirstSelectedMonth, _YearOfFirstSelectedMonth - 1)

VAR _A =
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Mo Cal Yr] ), [Total Assets] ),
FILTER (
ALL ( 'Date' ),
'Date'[MonthID] = _PriorMonth && 'Date'[Year] = _YearOfPriorMonth
)
)``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
P.S. Want me to build your Power BI solution?
Data Scientist | Data Engineer | Data Analyst | AI Engineer

➤ Website: https://sahirmaharaj.com

➤ Email: sahir@sahirmaharaj.com

➤ Lets connect on LinkedIn: Join my network of 12K+ professionals

➤ Want me to build your Power BI solution? Lets chat about how I can assist!

➤ Join my Medium community of 30k readers! Sharing my knowledge about data science and artificial intelligence

LinkedIn Top Voice in Artificial Intelligece, Data Science and Machine Learning

Helper III

Hi Shair,

I got a syntax error.  I'm not sure why.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors