Calculate the weekly average of a measurement

[Anonymous]

Hello,

I have this measure to calculate the number of distinct users per day who visit the reports. However I have to make a weekly average taking into account the five working days without the weekends. I guess I might need a calendar table that will be linked to the date field of my tf_audit table. Can you help me? 

# Audit.Vues =

var _table=SUMMARIZE(TF_AUDIT,TF_AUDIT[Audit.WorkspaceId],"distinctcount",DISTINCTCOUNT(TF_AUDIT[Audit.UserKey]))

return

SUMX(_table,[distinctcount])

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have created a simple sample, please refer to it to see if it helps you.

Create columns about weeknumber and weekday.

WEEKDAY = WEEKDAY(TF_AUDIT[date],1)

WEEKNUM = WEEKNUM(TF_AUDIT[date],1)

Measure =
CALCULATE (
    AVERAGE ( TF_AUDIT[Audit.UserKey] ),
    FILTER (
        ALL ( TF_AUDIT ),
        TF_AUDIT[WEEKNUM] = SELECTEDVALUE ( TF_AUDIT[WEEKNUM] )
            && TF_AUDIT[WEEKDAY] <= 5
    )
)

If I have misunderstood your meaning, please provide your pbix file without privacy information and your desired output.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hello thank you but it doesn't work in my case. I need to count the weekly average number of views per report. To help: one row = one view if the profile activity column = "Reader" I need to count the number of rows with "Reader" then make the weekly average of this number.

I make an example below of my table. The weekly average of the rows concerning the reader profiles must be done. Do not take into account the others as analyst. I have to sum the 1's of the volume column and calculate the weekly average and why per month if possible if we can make it dynamic.

I can only have one line max per person per report per day.

PROFIL	Volume View	Date	Name	REPORT
READER	1	02/01/2022	Jack	Sells
READER	1	03/01/2022	Jack	Sells
READER	1	02/01/2022	Tom	Sells
ANALYSTE	1	02/01/2022	Lisa	Sells

[amitchandak]

@Anonymous , Have these columns in table or date table

Start Year = STARTOFYEAR('Date'[Date],"3/31")
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
FY Year = YEAR('Date'[Start Year]) // use end year
FY Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[FY Year]=EARLIER([FY Year])),'Date'[Start of Week]),[Date],DAY),7)+1

Work Day = if(WEEKDAY([Date],2)>=6,0,1)

# Audit.Vues =
AverageX(values('Date'[Start of Week]) ,calculate(DISTINCTCOUNT(TF_AUDIT[Audit.UserKey]), filter('Date','Date'[Work Day] =1) ) )