Forum Discussion

Reddy5833's avatar
Reddy5833
Helper II
1 year ago

Filter dates in X axis dynamically in a column chart based on a date range selection

Hi,

Could you assist with the implementation of the following requirements?

if user choose any date range of

  • Date Range 1-4 Days: Display data for the last 10 days on the column chart, with each day represented by a separate column.
  • Date Range 5-10 Days: Show data from the last 15 days on the chart, again with each day represented by its own column.
  • Date Range of more than 10 Days: The chart should directly reflect the range selected by the user.

    I have a "Entries" fact table as below

     

    I need a line with column chart where distinct count of machine Id’s should be displayed in the column and count of entries be displayed in the line chart, the only thing is I want to control the displayed date rage in the x axis based on the selected date rage.

     

    I have tried to achieve this in couple of ways like using connected date dimension and another way is using the disconnected date table as well. but didn't work.

     

    Below is the dax measures I have used.

     

    To count the number of days selected: 

     

     

    Thanks in advance.

Need Help

2 Replies

  • suparnababu8's avatar
    suparnababu8
    Super User
    1 year ago

    Hi Reddy5833 

    First create this measure 

    DaysSelected = 
DATEDIFF(MIN('DateTable'[Date]), MAX('DateTable'[Date]), DAY)


    then create a Measure for Distinct Count of Machine IDs

    DynamicMachineCount = 
VAR MaxFactDate = MAX(Entries[Entry Date])
VAR SelectedMinDate = MIN('DateTable'[Date])
VAR SelectedMaxDate = MAX('DateTable'[Date])
VAR DaysRange = [DaysSelected]
RETURN
SWITCH(TRUE(),DaysRange <= 4, 
        CALCULATE(DISTINCTCOUNT(Entries[Machine ID]),
            Entries[Entry Date] >= MaxFactDate - 9 && Entries[Entry Date] <= MaxFactDate
        ),
    DaysRange > 4 && DaysRange <= 10, 
        CALCULATE(DISTINCTCOUNT(Entries[Machine ID]),
            Entries[Entry Date] >= MaxFactDate - 14 && Entries[Entry Date] <= MaxFactDate
        ),
    DaysRange > 10, 
        CALCULATE(DISTINCTCOUNT(Entries[Machine ID]),
            Entries[Entry Date] >= SelectedMinDate && Entries[Entry Date] <= SelectedMaxDate
        )
)

    the create Measure for Count of Entries 

    DynamicEntryCount = 
VAR MaxFactDate = MAX(Entries[Entry Date])
VAR SelectedMinDate = MIN('DateTable'[Date])
VAR SelectedMaxDate = MAX('DateTable'[Date])
VAR DaysRange = [DaysSelected]
RETURN
SWITCH(TRUE(),DaysRange <= 4,CALCULATE(
            COUNT(Entries[Entry ID]),
            Entries[Entry Date] >= MaxFactDate - 9 && Entries[Entry Date] <= MaxFactDate
        ),
    DaysRange > 4 && DaysRange <= 10, 
        CALCULATE(COUNT(Entries[Entry ID]),
            Entries[Entry Date] >= MaxFactDate - 14 && Entries[Entry Date] <= MaxFactDate
        ),
    DaysRange > 10, 
        CALCULATE(COUNT(Entries[Entry ID]),
            Entries[Entry Date] >= SelectedMinDate && Entries[Entry Date] <= SelectedMaxDate
        )
)

    Pls use this measures and I hope you'll get the expected output.
    Here you see my output below.

    Date from 1st to 6th

    Date from 1st to 5th




    • Reddy5833's avatar
      Reddy5833
      Helper II
      1 year ago

      Hi suparnababu8 ,

      Thanks for replying, but unfortunatly this not waht I'm looking for, my requirement is bit different. Apologies if my wording caused any confusion.

       

      My requirement is as below,

      for example, let’s say max entry date in my fact table is 09-Sep-2024, 

      if I choose the date range of 1-Jan-2024 to 4-Jan-2024 (4 days) from slicer, then it should display last 10 days on the x axis of the column chart that means from 31-Aug-2024 to 9-Sep-2024.

       

      similarly if the selected date range is more than 4 days (1-Jan-2024 to 5-Jan-2024) and <= 10 days (1-Jan-2024 to 10-Jan-2024), then we should show last 15 days ( 26-Aug-2024 to 9-Sep-2024) data in the chart.

      But if the selected date range is greater than 10 days (1-Jan-2024 to 11-Jan-2024) then show whatever the range selected by user, (show 1-Jan-2024 to 11-Jan-2024 data in the chart)

       

      Regards,