Check for previous day, including weekends

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