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 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
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ 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
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Helper III

Hi Shair,

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

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.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors