How filter Previous week and All Year Data

[ajitsahoo8338]

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"
)

[v-linhuizh-msft · Accepted solution]

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.

[DataNinja777]

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]

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]

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,

[ajitsahoo8338]

Can you please tell me how I can apply this to my visual  I am unable understand

Thank You