Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mauchchr
New Member

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:

 

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 viewModel viewExpenditures TableExpenditures TableSlicer TableSlicer Table

1 ACCEPTED SOLUTION
qqqqqwwwweeerrr
Super User
Super User

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 
qqqqqwwwweeerrr_0-1706342286485.png

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

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

qqqqqwwwweeerrr_1-1706342366502.png

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
 

View solution in original post

2 REPLIES 2
qqqqqwwwweeerrr
Super User
Super User

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 
qqqqqwwwweeerrr_0-1706342286485.png

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

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

qqqqqwwwweeerrr_1-1706342366502.png

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
 

Hi 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.