Rolling 2 week, 4 week average

[Diksha]

I have this table. What I want is rolling averages of 2 week, 4 week, 6 week and 8 week. Like in below table If I select date 15 from date master table. Then rolling 2 week is June 15 and June 8. So I want to calculate average of client subs like (140+147)/2. But issue is Client subs is calculated from the table and dax used 

#Clt. Subs = CALCULATE(DISTINCTCOUNT(_Activity[Note Template ID]),'Actions DM'[Action_Short]="Client Sub"). Can you please suggest how can I do this. 

[v-agajavelly]

Hi @Diksha ,

Since we haven’t heard back from you, we’ll proceed to close this thread for now. If you continue to experience issues or have any additional questions, feel free to start a new thread in the Microsoft Fabric Community Forum. We’re always here to help and happy to support you.

Regards,
Akhil.

[v-agajavelly]

Hi @Diksha ,

Just wanted to follow up and confirm that everything has been wrapped up on this. Please let me know if there’s anything pending from my side. Please feel free to reach out Microsoft fabric community forum.

Regards,
Akhil.

[v-agajavelly]

Hi @Diksha ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you,
Akhil.

[v-agajavelly]

Hi  @Diksha 

Just wanted to say a big thank you @SundarRaj  for the amazing solution you shared.

The rolling averages table now works exactly as diksha hoped clean, clear. The way sunder used List.FirstN in Power Query with the sample data was correct on point.  If anything, a dynamic date filter would make it even better, but this already gets the job done beautifully.

If there is any issue after following super user provided steps, please feel reach to Microsoft Fabric Community Forum.
_________________________________________________________________________________________________________________________
If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Reagrds,
Akhil.

[Greg_Deckler]

@Diksha See if this helps: Better Rolling Average - Microsoft Fabric Community

[Diksha]

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 · Accepted solution]

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=104...

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

[Diksha]

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.

[Diksha]

#Clt. Subs = CALCULATE(DISTINCTCOUNT(_Activity[Note Template ID]),'Actions DM'[Action_Short]="Client Sub")

#Sourc. Subs = CALCULATE(DISTINCTCOUNT(_Activity[Note Template ID]),'Actions DM'[Action_Short]="Source Sub")

IV = CALCULATE(DISTINCTCOUNT(_Activity[Note Template ID]),'Actions DM'[Action_Short]="Interview") 

I have measures like this. So now what my requirement is I want make a table in which 2 week, 4 week, 6 week and 8 weeks should be the columns name. and rows should be #Clt. Subs, #Sourc. Subs, IV like this. 

So, I want averages of 2 week, 4 week , 6 and 8 week. Can you please help. Solution given above does not actually solve my problem.