Period table for use in filters, issues with Yesterday

[mark_carlisle]

I've built a report and require some more advanced datetime filtering. I have used the guide on "https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi..." as this is very close to the sort of thing I'm looking for. I want to add in an option for Yesterday, however I am running into an issue.

Below are images of the joins and the Period table that I am having issues with;

As can be seen from the highlight Yesterday is including todays date. The query from the Advanced Editior is;

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Today", 
                TodaysDate, 
                TodaysDate, 
                1},
                {"Yesterday", 
                Date.AddDays(TodaysDate,-1), 
                TodaysDate, 
                2},
                {"Current Week To Date", 
                Date.From(Date.StartOfWeek(TodaysDate)), 
                TodaysDate, 
                3},
                {"Current Month To Date", 
                Date.From(Date.StartOfMonth(TodaysDate)), 
                TodaysDate, 
                4},
                {"Current Year To Date", 
                Date.From(Date.StartOfYear(TodaysDate)), 
                TodaysDate, 
                5},
                {"Rolling Week", 
                Date.AddWeeks(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                6},
                {"Rolling Month", 
                Date.AddMonths(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                7},
                {"Rolling Year", 
                Date.AddYears(TodaysDate,-1) + #duration(1,0,0,0), 
                TodaysDate, 
                8}
             },
    GetTables = List.Transform(Ranges, 
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)
    
in
    Output

The red text is what I have added in. Where am I going wrong?

I've thought of using Relative Date filters already built into Power BI but this does not allow me to do achieve my ultimate goal which is to have Yesterday include Friday, Saturday and Sunday when 'Today' is a Monday.

If there are any alternatives to do what I want to do then I'm open to suggestions. Unfortunatally it is not enough just to exclude Saturday and Sundays as data can be submitted on these days so they need to be accounted for.

[Greg_Deckler]

Getting an error related to "CreatePeriodTable", what is that? Is it a function?

[mark_carlisle · Accepted solution]

I've since resolved my issue. I don't fully understand what was going on in the first post as I'd just cheated and copied from the template supplied on the linked site.

I've since built my own date/period tables from scratch (source: https://powerbi.tips/2016/08/building-date-table-from-scratch/) and achieved what I wanted to.

To be fair this has been the better way as I've learnt rather than cheating.