Hi Team,
I have data in SQL as attached.
PayrollData.xlsx (9.6 KB)
In payroll data, I have group 1 paid in one week and group 2 paid in another week. Those 2 groups has to be combined. There are some weeks we do not process for group 1 or group 2.
I would like to present on power BI for last 4 fortnights as the following screenshot.
.
Is there any measure I can use to have that rolling report.
Thank you.
Regards,
Leo
Seems like this would be an absolutely perfect use case for applying the offset capability in the extended date table.
@melissa just produced a terrific video on this topic:
You could set up a SWITCH() or SWITCH( TRUE() ) that sets the week offset pattern based on presence in Group 1 or Group 2.
I hope this is helpful.
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.
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 
Here’s my sample file. eDNA - Report over last 4 fortnights.pbix (139.5 KB)
I hope this is helpful.
Hi @Melissa,
I could not believe what you have done for me.
It is now my turn to work with real data.
Thank you so much and greatly appreciated.
Regards,
Leo.
Hi @Melissa and Team,
I now have to modify the payroll report with Budget and leave figures as attached excel.
Please let me know how can I bring those in, while keeping the matrix as I will have to have drilled down for each facilities.
I also attached working sample file for power BI and excel.
Many thanks.
kindest regards,
ammu
Hi @ammu,
Asking a new question in an already Solved thread my result in not receiving any responses… please create a new thread for your follow up question and if you feel this thread is relevant add a link.
Thank you!
Thanks, Melissa.
I will create a new question.
Regards,
Aye
