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.
Hi,
I have a date column which has the list of 12 months of data like this:
And, I want this to split into bins like "Last 1 month", "Last 3 months", "Last 6 months", "Last 12 months",
so that for example: when I click on "Last 3 months", the data related to April, March and Feb 2019 should be displayed and when I click on "Last 6 months" - Data from November 2019 till April 2019 should be displayed.
Appreciate if someone could help me on this!
Thanks in advance,
Shashank.
Solved! Go to Solution.
You may try to create a measure and use it in visual level filter.For further,please refer to attached sample file.
Measure = VAR EToday = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 31 ) VAR Edate1 = EDATE ( EToday, -1 ) RETURN IF ( ISFILTERED ( Table1[Slicer] ), IF ( MAX ( Table1[Slicer] ) = "Last 1 month" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -2 ), 1, IF ( MAX ( Table1[Slicer] ) = "Last 3 months" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -4 ), 1, IF ( MAX ( Table1[Slicer] ) = "Last 6 months" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -7 ), 1, IF ( MAX ( Table1[Slicer] ) = "Last 12 months" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -13 ), 1 ) ) ) ), 1 )
Regards,
You may try to create a measure and use it in visual level filter.For further,please refer to attached sample file.
Measure = VAR EToday = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 31 ) VAR Edate1 = EDATE ( EToday, -1 ) RETURN IF ( ISFILTERED ( Table1[Slicer] ), IF ( MAX ( Table1[Slicer] ) = "Last 1 month" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -2 ), 1, IF ( MAX ( Table1[Slicer] ) = "Last 3 months" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -4 ), 1, IF ( MAX ( Table1[Slicer] ) = "Last 6 months" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -7 ), 1, IF ( MAX ( Table1[Slicer] ) = "Last 12 months" && MAX ( 'Date'[Date] ) <= Edate1 && MAX ( 'Date'[Date] ) > EDATE ( EToday, -13 ), 1 ) ) ) ), 1 )
Regards,
Hi You can use date slicer (Use Relative) Please find Attached PBIX File
Please Mark as a Solution if you found solution
Hey Vikram - Thanks for responding. However, that's not how I want.
I have month wise information, and my client wants to see it as Appreciate if you can help me.
Hey, Thanks for reply,
then you can use BookMark
Create 4 Button and give name respectivly and apply filter according and apply bookmark setting.
you can find bookmark in "View" Tab
Please find below link
Please Mark as a Solution if it's help.
Thanks
I did think about Bookmarks, Vikram however it is a brute solution to this problem, if you ask me.
I have a good amount data in the background to process and four different bookmarks and overlapping all visuals will only make the dashboard slow.
Was interested to know if there's a better and smart way of solving this problem.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |