Forum Discussion

CracktheCode85's avatar
CracktheCode85
Helper II
2 years ago
Solved

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? 

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  CracktheCode85 ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create calculated table.

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

    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:

     

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  CracktheCode85 ,

    I created some data:

     

    Here are the steps you can follow：

    1. Create calculated table.

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

    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:

     

    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

    • CracktheCode85's avatar
      CracktheCode85
      Helper II
      2 years ago

      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 🙂