Multiple Dates within one data set calculation

[SamBrown17]

Hi Team,

I hope you can help, I have a table with 3 different sales items

Assessing, Tendering and Works

They each have 3 different payments dates within the one table

I want to get the sum of all of them per month and also the average of all of them as one figure per month.

The average cost and the sum as a total query is easy enough, I just cant do it per month as I can only have one relationship and if I filter to one date it only gives me the average cost of only that item.

So I calculate the total average over the 3 items for the entire table

AVERAGE('reporting jobs'[assessment_repairCost])+AVERAGE('reporting jobs'[jobAmount])+AVERAGE('reporting jobs'[finalTenderAmount])/(COUNT('reporting jobs'[assessment_repairCost])+COUNT('reporting jobs'[jobAmount])+COUNT('reporting jobs'[finalTenderAmount]))

How do I do it per month when there is 3 different dates for each Assessing Cost, jobamount and final tender amount?

[SamBrown17]

Assessing Cost	Assessing Invoice Date	Tender Cost	Tender invoice data	Repair Cost	Repair Invoice Cost
1	1/03/2021	4	23/06/2021	100	1/01/2021
2	2/03/2021	6	24/06/2021	200	2/01/2021
3	3/03/2021	5	25/06/2021	300	3/01/2021
4	4/03/2021	4	26/06/2021	400	4/01/2021
5	5/03/2021	6	27/06/2021	500	5/01/2021
6	6/03/2021	7	28/06/2021	6600	6/01/2021
7	7/03/2021	3	29/06/2021	233	7/01/2021
8	8/03/2021	4	30/06/2021	234	8/01/2021

I want to Reference an average cost of all 3 revenue streams but I can use one date type otherwise I lose the other 2 revenues.

[v-kelly-msft]

Hi @SamBrown17 ,

Could you please provide some sample data with expected output?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[RJ251997]

Hi @SamBrown17 ,

Good Morning 

please go to that link its helpful for you.

https://blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/

Thank &Reagrds 

Rohit Jaiswal