Forum Discussion

DataSpace's avatar
DataSpace
Icon for Helper II rankHelper II
2 years ago
Solved

Set default 7 days dates to Date (between) slicer

I have a client requirement where Date (Between) Slicer need to set default for last one week as shown below:

From Date: date before 7 days from today.

To Date: today's date

 

                                   

 

 

Dataset having a main table where Date column having past as well as future date values.

Currenty Date (between) slicer showing min date and max date from dataset.

 

After lending report page with default last one week date values, User should be able to select any From and To date to filter the data.

 

Any workaround/suggestion/comments/tricks highly appreciated.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi DataSpace 

     

    Maybe you can try this:

    First of all, I create a set of sample:

     

    Then add a calculate column:

    Column =
VAR _CurrentDate =
    TODAY ()
RETURN
    IF (
        'Table'[Date]
            >= DATE ( YEAR ( _CurrentDate ), MONTH ( _CurrentDate ), DAY ( _CurrentDate ) - 7 )
            && 'Table'[Date] <= _CurrentDate,
        "LastOneWeek",
        FORMAT ( 'Table'[Date], "yyyy-mm-dd" )
    )

     This column mark the last 7 days as the LastOneWeek:

    The result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • AnalyticPulse's avatar
    AnalyticPulse
    Icon for Solution Sage rankSolution Sage
    2 years ago

    try adding below dax in your slicer;
    LastSevenDays =
    VAR CurrentDate = TODAY()
    VAR MinDate = CurrentDate - 6
    VAR MaxDate = CurrentDate
    RETURN CALCULATE( [Your_table], FILTER( ALL('YourDateColumn'), 'YourDateColumn' >= MinDate && 'YourDateColumn' <= MaxDate ) )

    let me know if you encounter any error...
    If this helped, Follow this blog for more insightful information about data analytics
    https://analyticpulse.blogspot.com/
    https://analyticpulse.blogspot.com/2024/04/commercial-real-estate-portfolio.html

    • DataSpace's avatar
      DataSpace
      Icon for Helper II rankHelper II
      2 years ago

      1. There should be some expression insted of scaler table as first argument in CALCULATE function.

      what it should be?

      2. How to assign this measure to slicer? How it will set required values to 'From Date' and 'To Date' in Date range(between) slicer?

       

      Please guide

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DataSpace 

     

    Maybe you can try this:

    First of all, I create a set of sample:

     

    Then add a calculate column:

    Column =
VAR _CurrentDate =
    TODAY ()
RETURN
    IF (
        'Table'[Date]
            >= DATE ( YEAR ( _CurrentDate ), MONTH ( _CurrentDate ), DAY ( _CurrentDate ) - 7 )
            && 'Table'[Date] <= _CurrentDate,
        "LastOneWeek",
        FORMAT ( 'Table'[Date], "yyyy-mm-dd" )
    )

     This column mark the last 7 days as the LastOneWeek:

    The result is as follow:

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • DataSpace's avatar
      DataSpace
      Icon for Helper II rankHelper II
      2 years ago

      Thanks Anonymous  for your response.

       

      This can work with Date (Dropdown) slicer, but how to map thi column with Date (Between) Slicer. 

      Because when it map Date column with Date(Between ) slicer, it takes by default takes min and max value from that column. so it will skill the hard code value 'Last one week'.

      Can you please explain a bit more to make me understand ?

       

      Regards,

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi DataSpace 

         

        The workaround doesn't applicable to the between Slicer, because the format of the calculate column is text.

        This workaround is to get the date of today by using TODAY() function, then mark the last 7 days of today as the LastOneWeek.

         

        Best Regards

        Zhengdong Xu
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.