Forum Discussion

ajitsahoo8338's avatar
ajitsahoo8338
Icon for Helper III rankHelper III
1 year ago
Solved

How filter Previous week and All Year Data

Hello Everyone ,

 

I need help with this isue.I created this to filter the previous week's data and all date data. However, the issue I am facing is that when the selection is 'All Date' it filters all data except for the previous week's data. I want all date data, including previous week's data, to be shown when the selection is 'All Date'. How can I fix this?

VAR CurrentDate = TODAY()
VAR LastSunday = (CurrentDate - WEEKDAY(CurrentDate, 1))-6 -- Get the last Sunday 
VAR LastSaturday = CurrentDate - WEEKDAY(CurrentDate, 1)  -- Saturday of the previous week
VAR DateValue = DateTable[Date]
RETURN 
IF(
    DateValue >= LastSunday && DateValue <= LastSaturday,
    "Previous Week",
    "All Date"
)
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the replies from DataNinja777.

     

    Hi ajitsahoo8338 ,

     

    Based on your description, I created two more tables, the Date table and the Slicer table with fields as slicers:

     

    Create two measures:

    Measure = 
VAR CurrentDate = TODAY()
VAR LastSunday = (CurrentDate - WEEKDAY(CurrentDate, 1))-6 -- Get the last Sunday 
VAR LastSaturday = CurrentDate - WEEKDAY(CurrentDate, 1)  -- Saturday of the previous week
VAR DateValue = MAX('DateTable'[Date])
RETURN 
IF(
    SELECTEDVALUE('Slicer'[SlicerValue]) = "All Date",
    "All Date",
    IF(
        DateValue >= LastSunday && DateValue <= LastSaturday,
        "Previous Week",
        "All Date"
    )
)
    Filter = 
SWITCH(SELECTEDVALUE('Slicer'[SlicerValue]),
"All Date",1,
"Previous Week",IF(MAX('DateTable'[Date])>=MIN('Date'[Date])&&MAX('DateTable'[Date])<=MAX('Date'[Date]),1,0))

     

    Filter visual using Filter measure:

     

    Result:

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

5 Replies

  • DataNinja777's avatar
    DataNinja777
    Icon for Super User rankSuper User
    1 year ago

    Hi ajitsahoo8338 ,

     

    To resolve the issue and ensure that the "All Date" category includes all data, including the previous week's data, you can adjust your DAX formula to account for the slicer selection dynamically. The current formula explicitly categorizes dates into "Previous Week" or "All Date," which causes the problem when "All Date" excludes the previous week.

    The updated formula uses the SELECTEDVALUE function to check the slicer selection. When the slicer selection is "Previous Week," the formula ensures only dates between the last Sunday and last Saturday are categorized as "Previous Week." When the slicer selection is "All Date," it categorizes all dates, including those in the previous week, as "All Date."

    Here is the revised formula:

    VAR CurrentDate = TODAY()
VAR LastSunday = (CurrentDate - WEEKDAY(CurrentDate, 1)) - 6  -- Get the last Sunday
VAR LastSaturday = CurrentDate - WEEKDAY(CurrentDate, 1)  -- Last Saturday
VAR DateValue = DateTable[Date]
RETURN 
IF(
    SELECTEDVALUE(FilterTable[Selection]) = "Previous Week",
    IF(DateValue >= LastSunday && DateValue <= LastSaturday, "Previous Week", BLANK()),
    "All Date"
)

    This formula assumes that there is a slicer or field, such as FilterTable[Selection], where users can choose between "Previous Week" and "All Date." The formula dynamically adjusts its behavior based on the selection.

     

    Best regards,

     

    • ajitsahoo8338's avatar
      ajitsahoo8338
      Icon for Helper III rankHelper III
      1 year ago

      Thank you DataNinja777  for your response. Your DAX is showing all date data when the selection is 'All Dates.' However, if I add a date slicer, it tries to filter some dates even when the selection is 'All Dates.' It is not filtering the date; it is showing all date data.

      • DataNinja777's avatar
        DataNinja777
        Icon for Super User rankSuper User
        1 year ago

        Hi ajitsahoo8338 ,

         

        Thank you for clarifying. If the requirement is to apply external filters, including the external date slicer, when "All Dates" is selected, the formula must allow the slicer context to take precedence while ensuring it categorizes data appropriately based on the selection. Here's the revised formula:

        VAR CurrentDate = TODAY()
VAR LastSunday = CurrentDate - WEEKDAY(CurrentDate, 1) - 6  -- Last Sunday
VAR LastSaturday = CurrentDate - WEEKDAY(CurrentDate, 1)   -- Last Saturday
VAR DateValue = DateTable[Date]
VAR SlicerSelection = SELECTEDVALUE(FilterTable[Selection], "All Date") -- Default to "All Date"
RETURN
SWITCH(
    SlicerSelection,
    "Previous Week",
        IF(DateValue >= LastSunday && DateValue <= LastSaturday, "Previous Week", BLANK()),
    "All Date",
        IF(ALLSELECTED(DateTable[Date]), "All Date", BLANK()), -- Applies external filters
    BLANK() -- Handle unexpected cases gracefully
)

        This formula handles the scenario where "All Dates" is selected by allowing the filter context from the external date slicer to remain active. The key is ensuring that the "All Date" logic does not overwrite or bypass slicer filters but still categorizes the data as "All Date."

        When "Previous Week" is selected, the formula continues to filter the data strictly for the range between LastSunday and LastSaturday, independent of the slicer. However, when "All Dates" is selected, it respects the external slicer context, applying those filters while categorizing the data as "All Date." This approach achieves the requirement to apply external filters even when "All Dates" is selected.

         

        Best regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the replies from DataNinja777.

     

    Hi ajitsahoo8338 ,

     

    Based on your description, I created two more tables, the Date table and the Slicer table with fields as slicers:

     

    Create two measures:

    Measure = 
VAR CurrentDate = TODAY()
VAR LastSunday = (CurrentDate - WEEKDAY(CurrentDate, 1))-6 -- Get the last Sunday 
VAR LastSaturday = CurrentDate - WEEKDAY(CurrentDate, 1)  -- Saturday of the previous week
VAR DateValue = MAX('DateTable'[Date])
RETURN 
IF(
    SELECTEDVALUE('Slicer'[SlicerValue]) = "All Date",
    "All Date",
    IF(
        DateValue >= LastSunday && DateValue <= LastSaturday,
        "Previous Week",
        "All Date"
    )
)
    Filter = 
SWITCH(SELECTEDVALUE('Slicer'[SlicerValue]),
"All Date",1,
"Previous Week",IF(MAX('DateTable'[Date])>=MIN('Date'[Date])&&MAX('DateTable'[Date])<=MAX('Date'[Date]),1,0))

     

    Filter visual using Filter measure:

     

    Result:

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.