Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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?
Solved! Go to Solution.
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
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
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 🙂
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |