topic Re: Number of updates per week in DAX Commands and Tips

Number of updates per week

SHILL — Fri, 05 Aug 2022 10:44:03 GMT

Hi All, I have the below table and I need to calculate how many times a colleague name appears each week. I could easily see this by setting up visuals however I want a formula so that on some visuals I can have it as an 'average per week' rather then a total.
For example - The below would be that Colleague 1 showed 0 times week 28 , once in week 29 and once in week 30.
Then if I selected to have a card saying weekly average for that colleague, it would be .66.

Colleague Name	Last Review Date	Case Id
Colleague 2	04/07/2022	#22
Colleague 3	06/07/2022	#54
Colleague 2	06/07/2022	#22
Colleague 2	11/07/2022	#31
Colleague 1	11/07/2022	#89
Colleague 3	19/07/2022	#55
Colleague 2	20/07/2022	#63
Colleague 2	20/07/2022	#22
Colleague 1	20/07/2022	#89
Colleague 3	20/07/2022	#55

Re: Number of updates per week

SpartaBI — Fri, 05 Aug 2022 10:52:29 GMT

SHILL what is a week represent in your data? All days from Sunday to Saterday? Monday to Sunday?

Re: Number of updates per week

SHILL — Fri, 05 Aug 2022 11:17:44 GMT

Hi, The week is Monday - Sunday.

Re: Number of updates per week

SpartaBI — Fri, 05 Aug 2022 12:01:59 GMT

SHILL
Here is a link to download a sample solution file:
Number of updates per week 2022-08-05.pbix

Showcase Report – Contoso By SpartaBI

Re: Number of updates per week

SHILL — Fri, 05 Aug 2022 12:31:08 GMT

Thanks Sparta - Unfortunately due to network restrictions within my workplace I am unable to download this - Is there a measure you can put in the comments?

Re: Number of updates per week

SpartaBI — Fri, 05 Aug 2022 12:41:35 GMT

SHILL sure. This is the measure:

Appearance Count = VAR _appearance = COUNTROWS('Table') VAR _total_weeks = CALCULATE(DISTINCTCOUNT('Table'[Week Num]), REMOVEFILTERS()) RETURN _appearance / _total_weeks

And this is how it looks in the file:

Showcase Report – Contoso By SpartaBI