Forum Discussion

modxplus's avatar
modxplus
Frequent Visitor
3 years ago
Solved

Check for previous day, including weekends

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"

 

 

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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's avatar
      modxplus
      Frequent Visitor
      3 years ago

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