Aggregate Daily data to Monthly with Average

[Richard_Halsall]

Hi could somebody help me with this requirement,

I have this simple table of data with a relationship to a date table 

Date__c	Opportunity__c	TechnicianRole	Technicians	Gap
18/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
17/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
16/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
15/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
14/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
13/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
12/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
11/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
10/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
09/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
08/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
07/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
06/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
05/09/2022	0063H000008N3r9QAC	Siemens Level 5	2	2
18/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
17/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
16/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
15/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
14/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
13/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
12/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
11/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
10/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
09/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
08/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
07/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
06/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1
05/09/2022	0063H000008N3qbQAC	Siemens Level 5	1	1

I am attempting to write a DAX formula to aggregate the above table to a monthly level grouped by Opportunity & TechnicianRole whilst averaging Technicians & Gap so the result would be:

Date__c	Opportunity__c	TechnicianRole	Technicians	Gap
Sept 22	0063H000008N3r9QAC	Siemens Level 5	2	2
Sept 22	0063H000008N3qbQAC	Siemens Level 5	1	1

I would use this DAX measure a line graph. Any advice would be appreciated. Thanks

[v-yadongf-msft · Accepted solution]

Hi @Richard_Halsall 

1.You can add a column called month like this:

2.Then create two measures like below:

Technicans = AVERAGE('fact'[Technicians])
Gap = AVERAGE('fact'[Gap])

3.Then on the report page, create a new table visual:

You can also create line chart by using the measures you created.

^{Best Regards,}

^{Yadong Fang}

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

[tamerj1]

Hi @Richard_Halsall 

you can slice by month and opportunity and simply USE MAX or MIN or SELECTEDVALUE to retrieve the respective comulm value