Forum Discussion
Rolling 2 week, 4 week average
Hi Diksha, from the data mentioned by you, I have created a dummy dataset and followed your outcome / output. Here's the file, the image of the output and the code in text for your reference. Is this what you are looking for? Do let me know. Thanks
File Link:
https://docs.google.com/spreadsheets/d/1sNn__L9XwoRIZcKDXt6W10BtZdzNZpls/export?format=xlsx&ouid=104752674875039603034&rtpof=true&sd=true
Here's the code:
let
Source = #table(
{
"Month",
"Day",
"Client Sub per Recruiter",
"Sourcing Sub per Recruiter",
"Average Sourcing Subs",
"Average Client Subs",
"Interviews",
"Offers",
"Temp Starts",
"Contract Billable HC",
"AWGP Run Rate"
},
{
{"June", 8, 4.83, 6.31, 169, 130, 19.5, 6.5, 6.0, 104, 60411},
{"June", 15, 4.45, 6.01, 165, 122, 17.5, 9.25, 7.5, 102, 55596},
{"June", 22, 4.68, 6.26, 170, 128, 23.0, 10.38, 7.38, 95, 54320},
{"June", 29, 4.95, 6.40, 174, 132, 21.0, 8.75, 6.75, 97, 56750},
{"July", 6, 5.10, 6.55, 178, 134, 22.5, 9.00, 7.00, 99, 57980},
{"July", 13, 4.88, 6.20, 171, 129, 20.0, 7.80, 6.50, 98, 55100},
{"July", 20, 4.70, 6.00, 167, 126, 18.5, 6.90, 6.25, 96, 53850},
{"July", 27, 4.92, 6.18, 173, 131, 19.8, 8.20, 6.90, 100, 56120}
}
),
#"Removed Columns" = Table.RemoveColumns(Source, {"Month", "Day"}),
Unpivot = Table.Unpivot(
#"Removed Columns",
{
"Client Sub per Recruiter",
"Sourcing Sub per Recruiter",
"Average Sourcing Subs",
"Average Client Subs",
"Interviews",
"Offers",
"Temp Starts",
"Contract Billable HC",
"AWGP Run Rate"
},
"Attribute",
"Value"
),
Group = Table.Group(
Unpivot,
{"Attribute"},
{
{"2-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 2)) / 2, 2), Int64.Type},
{"4-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 4)) / 4, 2), Int64.Type},
{"6-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 6)) / 6, 2), Int64.Type},
{"8-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 8)) / 8, 2), Int64.Type}
}
)
in
Group
Community ChampionDiksha See if this helps: Better Rolling Average - Microsoft Fabric Community
No, It does not solve my problem. I want table in this format :
2-weeks 4-weeks 6-weeks 8-weeks Sourcing Sub per Recruiter 6.31 6.01 6.23 6.26 Client Sub per Recruiter 4.83 4.45 4.65 4.68 Average Sourcing Subs 169 165 172 170 Average Client Subs 130 122 128 127 Interviews 19.50 17.50 21.00 23.00 Offers 6.50 9.25 9.83 10.38 Temp Starts 6.00 7.50 7.50 7.38 Contract Billable HC 104 102 99 95 AWGP Run Rate $60,411 $55,596 $54,937 $54,320 - SundarRaj
Super User
Hi Diksha, from the data mentioned by you, I have created a dummy dataset and followed your outcome / output. Here's the file, the image of the output and the code in text for your reference. Is this what you are looking for? Do let me know. Thanks
File Link:
https://docs.google.com/spreadsheets/d/1sNn__L9XwoRIZcKDXt6W10BtZdzNZpls/export?format=xlsx&ouid=104752674875039603034&rtpof=true&sd=true
Here's the code:
let
Source = #table(
{
"Month",
"Day",
"Client Sub per Recruiter",
"Sourcing Sub per Recruiter",
"Average Sourcing Subs",
"Average Client Subs",
"Interviews",
"Offers",
"Temp Starts",
"Contract Billable HC",
"AWGP Run Rate"
},
{
{"June", 8, 4.83, 6.31, 169, 130, 19.5, 6.5, 6.0, 104, 60411},
{"June", 15, 4.45, 6.01, 165, 122, 17.5, 9.25, 7.5, 102, 55596},
{"June", 22, 4.68, 6.26, 170, 128, 23.0, 10.38, 7.38, 95, 54320},
{"June", 29, 4.95, 6.40, 174, 132, 21.0, 8.75, 6.75, 97, 56750},
{"July", 6, 5.10, 6.55, 178, 134, 22.5, 9.00, 7.00, 99, 57980},
{"July", 13, 4.88, 6.20, 171, 129, 20.0, 7.80, 6.50, 98, 55100},
{"July", 20, 4.70, 6.00, 167, 126, 18.5, 6.90, 6.25, 96, 53850},
{"July", 27, 4.92, 6.18, 173, 131, 19.8, 8.20, 6.90, 100, 56120}
}
),
#"Removed Columns" = Table.RemoveColumns(Source, {"Month", "Day"}),
Unpivot = Table.Unpivot(
#"Removed Columns",
{
"Client Sub per Recruiter",
"Sourcing Sub per Recruiter",
"Average Sourcing Subs",
"Average Client Subs",
"Interviews",
"Offers",
"Temp Starts",
"Contract Billable HC",
"AWGP Run Rate"
},
"Attribute",
"Value"
),
Group = Table.Group(
Unpivot,
{"Attribute"},
{
{"2-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 2)) / 2, 2), Int64.Type},
{"4-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 4)) / 4, 2), Int64.Type},
{"6-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 6)) / 6, 2), Int64.Type},
{"8-Week Average", each Number.Round(List.Sum(List.FirstN(_[Value], 8)) / 8, 2), Int64.Type}
}
)
in
Group where each is measure.
Per RDM =CALCULATE(DIVIDE([#Sourc. Subs], [Full Production], 0))- Greg_Deckler
Diksha Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
