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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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