Forum Discussion
Filter dates in X axis dynamically in a column chart based on a date range selection
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
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,