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...
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.

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,

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

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

Thank You