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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
CracktheCode85
Helper II
Helper II

PREVIOUSMONTH DAX Function

Hi brilliant folks! 

I'm having a unique experience where my PREVIOUSMONTH DAX function is returning a balance for the FUTURE month instead of the PREVIOUS one... 

I've used a custom calendar and a the build in date range in my SQL query but they yeild the same results... 

 

Please let me know if you've encountered this before and how do you correct it? 

CracktheCode85_1-1716907730123.png

CracktheCode85_2-1716907733055.png

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @CracktheCode85 ,

I created some data:

vyangliumsft_0-1716950706724.png

 

Here are the steps you can follow:

1. Create calculated table.

Date =
CALENDAR(
    DATE(2024,1,1),DATE(2024,5,1))

vyangliumsft_1-1716950706725.png

2. You can try using the following measure.

Test =
var _select=SELECTEDVALUE('Table'[Date])
var _maxdate=EOMONTH(_select,-1)
var _mindate=DATE(YEAR(_maxdate),MONTH(_maxdate),1)
return
SUMX(
    FILTER('Value_Table',
    'Value_Table'[Date]>=_mindate&&'Value_Table'[Date]<=_maxdate),[Value])

3. Result:

 

vyangliumsft_2-1716950751937.png

If the above doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @CracktheCode85 ,

I created some data:

vyangliumsft_0-1716950706724.png

 

Here are the steps you can follow:

1. Create calculated table.

Date =
CALENDAR(
    DATE(2024,1,1),DATE(2024,5,1))

vyangliumsft_1-1716950706725.png

2. You can try using the following measure.

Test =
var _select=SELECTEDVALUE('Table'[Date])
var _maxdate=EOMONTH(_select,-1)
var _mindate=DATE(YEAR(_maxdate),MONTH(_maxdate),1)
return
SUMX(
    FILTER('Value_Table',
    'Value_Table'[Date]>=_mindate&&'Value_Table'[Date]<=_maxdate),[Value])

3. Result:

 

vyangliumsft_2-1716950751937.png

If the above doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Thank you!! 
This did help tremendously! I found out that the SQL Database we are using does have month end data so it does work with that- however creating the calendar is crucial for most dashboards 🙂 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.