Dynamic X-axis based on date selection in slicer

[mauchchr]

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:

Period Filter =

VAR SelectedSlicerDate = SELECTEDVALUE(Slicer[SlicerDate])

VAR RowDate = VALUE(Expenditures[DueDate])

VAR StartDate = SelectedSlicerDate - 365

VAR EndDate = SelectedSlicerDate

RETURN(IF(AND(RowDate >= StartDate, RowDate <= EndDate), Expenditures[Amount], BLANK()))

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

[qqqqqwwwweeerrr · Accepted solution]

Hi @mauchchr 

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 measure 

Measure =

  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 visual 

use measure in y-axis and due date in x-axis

and use date in filter that you have created in step 1

this hsould solve your probelm 

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

Regards

[mauchchr]

Hi 

Thank you for the suggested solution, it works very well.