Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have a visual where I would like to display only data for the last 12 complete months. For example, if today is 5/12/2021, the data I would like to be displayed would be running from 4/1/2020 to 4/30/2021. Every month has a full month's worth of data.
Right now I have this DAX, but PBI gives me errors on formatting and values when comparing the dates for some reason.
Solved! Go to Solution.
Hope you tried my solution to your problem, let me know if it works.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
This is the DAX forum so it's no surprise you've got a DAX answer. I'd just point out this functionality is available in the UI. Use a relative date slicer or filter with calendar month.
Hi Paul. You are correct that it is available in the UI. But if you notice, this does not meet my requirements as the data that is being filtered in does not start at the beginning and end of each month.
@matsahiro
Please use the following formula to have the desired results:
Rolling 12 Month Ind =
VAR CurrentDate = SELECTEDVALUE(Sales[Date_Sold])
VAR Start_Date =
EOMONTH( TODAY() , -14 )+1
VAR End_Date =
EOMONTH ( TODAY(), -1 )
RETURN
IF ( CurrentDate >= Start_Date && CurrentDate <= End_Date, 1, 0 )
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hope you tried my solution to your problem, let me know if it works.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Definitely gave it a go. The IF statement ended up not working for a specific visual I had, but I simply threw in the same logic in a CALCULATE statement.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |