Forum Discussion

Victormar's avatar
Victormar
Helper V
2 years ago

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 data date, if that makes sense.

 

Until now I was using a date column coming from a date table, based on the minimum sending date, but I would like to create a new personalized measure for the date, that would take the contract start date as the starting date, even if there was data sent before (that could happen) and the latest date where the vehicle sent data, and that would be applied to a date filter that I have for the visualizations.

 

Is that possible?

 

Cheers 😄

Need Help

4 Replies

  • isjoycewang's avatar
    isjoycewang
    Solution Supplier
    2 years ago

    Hi Victormar,

     

    Please consider below measure and add it into the visual level filter. Thanks.

    Start from Contract Start Date = 
IF( CALCULATE(MIN('Table'[Contract Start Date])) < SELECTEDVALUE('Table'[Sent Date]),1,0)

     

    Best Regards,

    Joyce 

     

    • Victormar's avatar
      Victormar
      Helper V
      2 years ago

      Thanks for your help. I tried it but the visual doesn't change, it's always the same. Could it be that the filters are not made to dynamically change?

      • isjoycewang's avatar
        isjoycewang
        Solution Supplier
        2 years ago

        It should be able to change. Please share some dummy data and your chart so we could have more information, thanks!

  • Victormar's avatar
    Victormar
    Helper V
    2 years ago
    VehicleContract Start Date
    A2022-10-22
    B2021-05-04
    C2021-09-06
    D2021-08-03

     

    VehicleDateValue
    A2023-10-191000
    A2023-10-182000
    A2023-10-173000
    B2023-10-182500

     

    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 🙂