Forum Discussion
Dynamic X-axis based on date selection in slicer
Hi everybody
I would like to create a bar chart with the expenditures on the Y-axis and the date on the X-axis. Based on the selected date in the slicer, e.g. 30.06.2023, the X-axis should display a time horizon of one year with the selected date in the slicer as the end date. In this example the start date would be 30.06.2022 with end date 30.06.2023.
The model consists of two tables "Expenditures" and "Slicer". The two tables have the following relationship with each other:
Expenditures[Due Date] : Slicer[Slicer Date] with one to one cardinality (1:1)
I have tried to create it with a column measure, but to no avail. The column measure looks like this:
Does anybody know how to implement a dynamic date X-axis based on a selected date in a slicer?
Thank you for your support.
Model viewExpenditures TableSlicer Table
Hi Anonymous
This can be done by following steps:
1. Create a new table for dates and make sure this not have any relationship with you these two primary table
Date = DISTINCT(Sheet3[Date]), you can create this based on duedate of your table
2. create this measureMeasure =var current_date = MAX('Date'[Date])var pervious_date = DATE(YEAR(current_date),MONTH(current_date)-12,DAY(current_date))var result = CALCULATE(SUM(Sheet3[Value]),FILTER(Sheet3,Sheet3[Date]>= pervious_date && Sheet3[Date]<= current_date))return result
and create visualuse measure in y-axis and due date in x-axis
and use date in filter that you have created in step 1this hsould solve your probelm
Regards
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem
2 Replies
qqqqqwwwweeerrrSolution Sage
Hi Anonymous
This can be done by following steps:
1. Create a new table for dates and make sure this not have any relationship with you these two primary table
Date = DISTINCT(Sheet3[Date]), you can create this based on duedate of your table
2. create this measureMeasure =var current_date = MAX('Date'[Date])var pervious_date = DATE(YEAR(current_date),MONTH(current_date)-12,DAY(current_date))var result = CALCULATE(SUM(Sheet3[Value]),FILTER(Sheet3,Sheet3[Date]>= pervious_date && Sheet3[Date]<= current_date))return result
and create visualuse measure in y-axis and due date in x-axis
and use date in filter that you have created in step 1this hsould solve your probelm
Regards
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem