Units per year per activity

[ZoltanT]

Hi

I am new to this so this is most likely a simple one. 

I need to count the amount of times (units) an activity occured during a financial year. Eg.

Activity	Financial Year	Units
Activity 1	2018	3
Activity 1	2018	3
Activity 1	2018	3
Activity 2	2018	2
Activity 2	2018	2
Activity 3	2018	1
Activity 4	2018	1
Activity 1	2019	2
Activity 1	2019	2
Activity 2	2019	1
Activity 3	2019	4
Activity 3	2019	4
Activity 3	2019	4
Activity 3	2019	4
Activity 4	2019	2
Activity 4	2019	2

Thank you in advance

[Ashish_Mathur]

Hi,

This calculated column formula works

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Activity]=EARLIER(Data[Activity])&&Data[Financial Year]=EARLIER(Data[Financial Year])))

Hope this helps.

[v-jayw-msft · Accepted solution]

Hi @ZoltanT ,

You can use ALLEXCEPT() function.

units = CALCULATE(COUNT('Table'[Activity]),ALLEXCEPT('Table','Table'[Activity],'Table'[Financial Year]))

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

@ZoltanT ,You can use count(Table[Activity]) , sum(Table[Unit]) in any visual with row/group by as finanacial year

[Ashish_Mathur]

Hi,

Please show the exact result that you are expecting?

[ZoltanT]

Hi @Ashish_Mathur 

The results I am expecting is in the third colum (units) shown in my example