Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Filter by Last Working Day

I need to be able to apply a slicer for "Today", "Yesterday" and "Older".

However, the data is not relevant on weekends so, when today is Monday, I need the report to show Friday's data when the "Yesterday" filter is applied.

 

I have the following at the moment, whch works fine, except for Monday:

 

Slicer = IF(DATEADD(Date_Lookup(Date),1,DAY) = TODAY(),"Yesterday",

              IF(DATE_Lookup(Date) = TODAY(),"Today",

              "Older"))

Need Help

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    Try this formula, please.

    Slicer New =
IF (
    [Date] > TODAY (),
    "NA",
    IF (
        [Date] = TODAY (),
        "Today",
        IF (
            WEEKDAY ( [Date], 2 ) = 5
                && DATEDIFF ( [Date], TODAY (), DAY ) <= 3
                && DATEDIFF ( [Date], TODAY (), DAY ) > 0,
            "Yesterday",
            IF ( DATEADD ( 'Date_Lookup'[Date], 1, DAY ) = TODAY (), "Yesterday", "Older" )
        )
    )
)

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

     

    Best Regards,

    Dale