Forum Discussion

UK_User123456's avatar
UK_User123456
Resolver I
3 years ago

Averages

Hi All,   I have two tables that are connected through a calendar table. The problem I am having is that in the first table I need to calculate an average of the last 12 months only even if the dat...
Need Help
Manoj_Nair's avatar
Manoj_Nair
Solution Supplier
3 years ago

UK_User123456 - Please check this out, let me know if this works. If this fix your problem, please tick this a solution and a thumps up.

 

Staff 12 MONTHS = 
CALCULATE(
    COUNTROWS('DEPARTMENT'),
    FILTER(
        ALL('Calendar'[Date]),
        'Calendar'[Date] > TODAY() - 365 && 'Calendar'[Date] <= TODAY()
    ),
    RELATEDTABLE('DEPARTMENT')
)
Total Staff = COUNTROWS('STAFF')
Average = 
DIVIDE(
    [Staff 12 MONTHS],
    [Total Staff],
    0
)

 

UK_User123456's avatar
UK_User123456
Resolver I
3 years ago

Hi Manoj_Nair ,

 

How would the calculation work if put into the calculation I currently have in my post, as I need to be able to show these split down by month, so showing the split out by month in a matrix table?

 

Thanks