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 line chart using year and month on the x axis and a measure on the y axis.
Is there a way we can use a slicer and show the data in the chart for months only prior to the selection.
Lets say we select March 20, the chart should show data from March 20 backwards.. even if we have data till July 20.
Is this possible?
Solved! Go to Solution.
Hi, @PBIfanatic
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Calendar(a calculated table) :
Calendar = CALENDARAUTO()
You may create calculated columns and measures as below.
Calculated column:
Calendar Year-Month = YEAR('Calendar'[Date])*100+MONTH('Calendar'[Date])
Table Year-Month = YEAR('Table'[Date])*100+MONTH('Table'[Date])
Measure:
Visual Control =
IF(
SELECTEDVALUE('Table'[Table Year-Month])<SELECTEDVALUE('Calendar'[Calendar Year-Month]),
1,0
)
Finally you may put the measure in the visual level filter and use the date column from ‘Calendar’ table to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @PBIfanatic
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Calendar(a calculated table) :
Calendar = CALENDARAUTO()
You may create calculated columns and measures as below.
Calculated column:
Calendar Year-Month = YEAR('Calendar'[Date])*100+MONTH('Calendar'[Date])
Table Year-Month = YEAR('Table'[Date])*100+MONTH('Table'[Date])
Measure:
Visual Control =
IF(
SELECTEDVALUE('Table'[Table Year-Month])<SELECTEDVALUE('Calendar'[Calendar Year-Month]),
1,0
)
Finally you may put the measure in the visual level filter and use the date column from ‘Calendar’ table to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi @PBIfanatic
A date slicer when used will always show data for the selected date and nothing pre or post the selected date.
I would use the input parameter instead in your case. below is the link to use Input parameter. use the below linki to get more details on it.
https://www.antaresanalytics.net/post/2018/09/11/power-bi-creating-user-input-variables
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |