If then Statement using Two Different Date Ranges

Question

In Power BI, I'm using a period table that applies steps to take today's date, sets up the ranges, transforms the ranges into a table, and then sets the output in Power Query. Everything works as it is supposed to but the focus is at the ranges step.

Here it is as is:

    = {
                    {"Day", 
                    Date.AddDays(TodaysDate,-1), 
                    Date.AddDays(TodaysDate,-1),
            1},
                    {"Week to Date", 
                    Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)), 
                    Date.AddDays( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , +6), 
            2},
                    {"Week", 
                    Date.AddWeeks( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , -1), 
                    Date.AddDays( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , -1), 
....

Here is the example output:

What I want however, is that if today is equal to Monday, then only show the date for Friday. Here was my attempt:

= {
            {"Day", 
            if Date.DayofWeek(TodaysDate) = 1 
       then 
    [
            Date.AddDays(TodaysDate,-3), 
            Date.AddDays(TodaysDate,-3),
    ]
            else
    [
            Date.AddDays(TodaysDate,-1), 
            Date.AddDays(TodaysDate,-1),
    ] ....

Notice that "Day" is "Yesterday". I'm trying to get this done so people can see production for the previous day worked where the previous day for Monday would be Friday.

Here is the whole code:

    let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Day", 
                Date.AddDays(TodaysDate,-1),                 
                Date.AddDays(TodaysDate,-1), 

        1},
                {"Week to Date", 
                Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)), 
                Date.AddDays( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , +6), 
        2},
        {"Week", 
                Date.AddWeeks( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , -1), 
                Date.AddDays( Date.From(Date.StartOfWeek(TodaysDate,Day.Thursday)) , -1), 
        3},
                {"Month To Date", 
                Date.From(Date.StartOfMonth(TodaysDate)), 
                TodaysDate, 
                4},
                {"Month", 
                Date.AddMonths(Date.From(Date.StartOfMonth(TodaysDate)),-1), 
                Date.AddMonths(Date.From(Date.EndOfMonth(TodaysDate)),-1), 
                5},
                {"Rolling 13Wks", 
                Date.AddWeeks(TodaysDate,-13) + #duration(1,0,0,0), 
                TodaysDate, 
                6},
                {"Year To Date", 
                Date.From(Date.StartOfYear(TodaysDate)), 
                TodaysDate, 
                7}

             },
    GetTables = List.Transform(Ranges, 
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables),
    #"Filtered Rows" = Table.SelectRows(Output, each true)
in
    #"Filtered Rows"

Answer 1

I think the simplest thing would be to create a variable for the previous day:

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    PrevWorkDate = if Date.DayOfWeek(TodaysDate) = 1
                   then Date.AddDays(TodaysDate,-3)
                   else Date.AddDays(TodaysDate,-1),

    Ranges = {
                {"Day", 
                PrevWorkDate,                 
                PrevWorkDate, 

        1},

    [...etc...]

---

Otherwise, you might be able to do something like this:

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
        if Date.DayOfWeek(TodaysDate) = 1
        then
            {"Day", 
                Date.AddDays(TodaysDate,-3), 
                Date.AddDays(TodaysDate,-3),
             1}
        else
            {"Day", 
                Date.AddDays(TodaysDate,-1), 
                Date.AddDays(TodaysDate,-1),
             1},