Forum Discussion
Personalized date filter
Hello community, I have a report where I have visualizations based on the sending date for the data. This data belongs to vehicles, and they also have a contract start date, and a latest sending ...
| Vehicle | Contract Start Date |
| A | 2022-10-22 |
| B | 2021-05-04 |
| C | 2021-09-06 |
| D | 2021-08-03 |
| Vehicle | Date | Value |
| A | 2023-10-19 | 1000 |
| A | 2023-10-18 | 2000 |
| A | 2023-10-17 | 3000 |
| B | 2023-10-18 | 2500 |
The I have a calendar table created dinamically basec on min(readings[date]), like:
DIM_Calendar =
VAR StartDate = MIN(Readings([contract_start_date]) ---> Columna a cambiar
VAR YearStartDate = YEAR(StartDate)
VAR Dates =
FILTER(
CALENDARAUTO(),
YEAR( [Date]) >= YearStartDate && YEAR([Date])<= YEAR(TODAY())
)
RETURN
ADDCOLUMNS(
Dates,
"Year", YEAR([Date]),
"Year Month", YEAR([Date]) & "-" & FORMAT([Date], "mm"),
"Quarter", FORMAT([Date], "\QQ"),
"Month Num", MONTH([Date]),
"Month Name", FORMAT([Date], "mmm"),
"Month", FORMAT([Date], "mm"),
"Month of Year", FORMAT([Date], "mmm") & "-" & YEAR([Date]),
"Week",WEEKNUM([Date]),
"Week Day Num", WEEKDAY([Date],2),
"Week Day", FORMAT([Date], "ddd"),
"Day", DAY([Date])
)
Once I have created this table, I use the date to add it to the displayed filter, and then I create a line chart where I have date in the x axis and the readings in the y axis. Also I have a filter to select which vehicle/fleet/customer I want, and my desire would be that the date filter would show the contract_start_date as the initial value, and the max reading date as the max value, according to my selection:
Thanks again 🙂