Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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...
Need Help
  • qqqqqwwwweeerrr's avatar
    qqqqqwwwweeerrr
    2 years ago

    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 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
     
qqqqqwwwweeerrr's avatar
qqqqqwwwweeerrr
Icon for Solution Sage rankSolution Sage
2 years ago

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 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
 
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi 

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