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
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 |
---|---|
115 | |
95 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |