Calculate Total for the Current Year

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 1...

Need Help

Tips and Tricks

Tutorial Requests

Anonymous
5 years ago
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.
Calculate Total for the Current Year.pbix43 KB

amitchandak

Super User

5 years ago

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

Helper I

5 years ago

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

Forum Discussion

Calculate Total for the Current Year