Forum Discussion

SamBrown17's avatar
SamBrown17
Helper I
5 years ago

Multiple Dates within one data set calculation

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?
Need Help
Show and Tell

3 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!

  • SamBrown17's avatar
    SamBrown17
    Helper I
    5 years ago
    Assessing CostAssessing Invoice DateTender CostTender invoice dataRepair CostRepair Invoice Cost
    11/03/2021423/06/20211001/01/2021
    22/03/2021624/06/20212002/01/2021
    33/03/2021525/06/20213003/01/2021
    44/03/2021426/06/20214004/01/2021
    55/03/2021627/06/20215005/01/2021
    66/03/2021728/06/202166006/01/2021
    77/03/2021329/06/20212337/01/2021
    88/03/2021430/06/20212348/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.