Check for previous day, including weekends

[modxplus]

Basically what i need is if "Today" is Tuesday through Friday i just want to populate the previous date. Ex. run on 5/10/23 will populate 5/9/2023

But if its "Monday" i want to populate Friday, Saturday and Sunday. Ex. run on 5/8/2023 i want to populate 5/7/2023, 5/6/2023 and 5/5/2023

The only thing i can find is excluding weekends, I need to include them. 
The last couple steps are just a "Check" column that i match back up to the main table that has activties and filter down to where there is a "Check"

let
    Date = DateTime.LocalNow(),
    MinusOneDayMondayMinus3Days = if Date.DayOfWeek(Date,0)= 1 then Date.AddDays(Date,-3) else Date.AddDays(Date,-1),
    #"Converted to Table" = #table(1, {{MinusOneDayMondayMinus3Days}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Check", each if [Column1] <> null then 1 else null)
in
    #"Added Conditional Column"

[BA_Pete · Accepted solution]

Hi @modxplus ,

Try adding a new custom column to your calendar table like this:

let Date.Today = Date.From(DateTime.LocalNow()) in
if
    (
        Date.DayOfWeek(Date.Today, 1) = 0
            and [date] >= Date.AddDays(Date.Today, -3)
            and [date] <= Date.AddDays(Date.Today, -1)
    )
    or [date] = Date.AddDays(Date.Today, -1)
then "Prev Period"
else null

You can then filter your visual/page/report on [NewColumn] = "Prev Period".

Full example query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc65DYAwFAXBXhwj+b815qjFov82IGTDyWatVuk1O8Voz/YRc5i7Oc3DPM3LvMWU6VW8ilfxKl7Fq3gVr+IVXuEVXuEVXuEVXuEVXuHV8Gr8Vs8L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"date", type date}}),
    #"Added Custom" =
        Table.AddColumn(
            chgTypes,
            "filterPrevPeriod",
            each let Date.Today = Date.From(DateTime.LocalNow()) in
            if
                (
                    Date.DayOfWeek(Date.Today, 1) = 0
                        and [date] >= Date.AddDays(Date.Today, -3)
                        and [date] <= Date.AddDays(Date.Today, -1)
                )
                or [date] = Date.AddDays(Date.Today, -1)
            then "Prev Period"
            else null
        )
in
    #"Added Custom"

You can change Date.DayOfWeek(Date.Today, 1) to Date.DayOfWeek(Date.Today, Day.Wednesday) to check that it works for the last three days at the end of a weekend too.

Pete

[modxplus]

Custom column worked great. Hadnt thought about using that. Thanks for your help!