Calculate Total for the Current Year

[lancea]

I have the following table:

ID	Date Description	Date Month	Date Year	Sales
1	Dec-2020	12	2020	100
2	Jan-2021	1	2021	100
3	Jan-2021	1	2021	50
4	Feb-2021	2	2021	100
5	Mar-2021	3	2021	25
6	Mar-2021	3	2021	75
7	Apr-2021	4	2021	100
8	Apr-2021	4	2021	100

I would like to get the following table:

Date Description	Monthly Total	Overall Year Total	Average Year Total
Jan-2021	150	550	137.5
Feb-2021	100	550	137.5
Mar-2021	100	550	137.5
Apr-2021	200	550	137.5

I was already able to get the Monthly Total just by summing Sales column. How do I create a measure / calculated column to compute for the Overall Year Total and Average Year Total?

[v-yetao1-msft · Accepted solution]

Hi @lancea 

You can create measures to return Overall Year Total and Average Year Total .

Monthly total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Description]))

Overall Year Total = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Date Year]))

Average Year Total = [Overall Year Total]/CALCULATE(DISTINCTCOUNT('Table'[Date Description]),ALLEXCEPT('Table','Table'[Date Year]))

Then put the measures with fields ‘Date Description’ and ‘Date Year’ in a new table visual ,like this

In ‘Date Year’ filter , filter out data for 2020 .

I have attached my pbix file , you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@lancea , Try measures like, with help from date table

This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] = year(today()) ) )

overall This year Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER(ALL('Date'),'Date'[Date] = year(today()) ) )

overall avg This year Today =
var _min = today()
return
CALCULATE(Avergae('Table'[Sales]), FILTER(ALL('Date'),'Date'[Date] = year(today()) ) )

[lancea]

Thank you for this! The Overall Year Total works! However, I am getting incorrect result from the average as seen in the screenshot below: