Reporting for every two weeks together for last 3 fortnights

Power Query Data Setup
3

Hi @ammu,

I was experimenting with the dynamic Period table, you can find details on that below in this post.

Amended that code to suite your needs. This code returns the current week (Week0) and 4 week pairs. Disabled load for the Period table and Merged it with the Date table.

let
    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
                {"Week0" , Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, 0), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, 0), Day.Monday)), 0},
                {"Week1", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -1), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -1), Day.Monday)), 1},
                {"Week1", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -2), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -2), Day.Monday)), 2},
                {"Week2", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -3), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -3), Day.Monday)), 3},
                {"Week2", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -4), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -4), Day.Monday)), 4},
                {"Week3", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -5), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -5), Day.Monday)), 5},
                {"Week3", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -6), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -6), Day.Monday)), 6},
                {"Week4", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -7), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -7), Day.Monday)), 7},
                {"Week4", Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -8), Day.Monday)), Date.From(Date.EndOfWeek(Date.AddWeeks(TodaysDate, -8), Day.Monday)), 8}
             },
    fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
        let
            DayCount = Duration.Days(EndDate-StartDate)+1,
            DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
            AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
            CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
        in
            CreateTable,
    
    GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables),
    #"Added WeekEnding" = Table.AddColumn( Output, "WeekEnding", each "Ending " & Text.From( Date.Day( Date.EndOfWeek( [Date], Day.Monday))) &" " & Text.Start(Text.From( Date.MonthName( Date.EndOfWeek( [Date], Day.Monday))), 3), type text ),
    TableBuffer = Table.Buffer( #"Added WeekEnding" ),
    #"Added ShiftForward" = Table.AddColumn( #"Added WeekEnding", "ShiftForward", each Table.Sort(Table.SelectRows(TableBuffer, (BT)=> BT[Period] = _[Period] ),{{"Date", Order.Descending}}){0}[WeekEnding], type text )
in
    #"Added ShiftForward"

.
Created a simple measure.

Total Value = SUM( Payroll[Value] )
.
With this result.
image

Now keep in mind that the 4 fortnight periods are fully dynamic, when the Queries are refreshed.
I’m not sure that meets your requirement but I had great fun designing it :wink:

Here’s my sample file. eDNA - Report over last 4 fortnights.pbix (139.5 KB)
I hope this is helpful.

3 Likes