topic Re: Annual Total for Each Item in DAX Commands and Tips

Annual Total for Each Item

IsaacAsher — Wed, 19 Aug 2020 18:58:35 GMT

The data I'm working with contains the "Date" and "Sales" columns shown in the example below. The date column will not have data on every day as it represents working days only. I'm trying to write some DAX to get the "Ann Total". This should represent the sum of Sales for each calendar year, and be repeated at the individual date level.

I have mocked up the data using simple numbers so it's easy to see the change in "Ann Total".

Any input would be greatly appreciated!

Re: Annual Total for Each Item

Anonymous — Wed, 19 Aug 2020 19:11:02 GMT

Hi IsaacAsher ,

You need to have a date calendar and mark it as the date in model view.
Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Post which you can pull in the Year Column from the Calendar Table

and create a measure

Sales = SUM(Table[Sales])

For this scenario (not recommended though)

Create a Column in your Table

YEAR = Year (Table[Date])

Then create a measure

Measure = CALCULATE (SUM(Table[Sales]), ALLEXCEPT(Table, Table[YEAR]))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Annual Total for Each Item

IsaacAsher — Wed, 19 Aug 2020 21:32:46 GMT

Thank you Anonymous

Unfortunately when I tried this trick, it gave me the same results in the measure (Ann Total) as the Sales column.

Re: Annual Total for Each Item

vivran22 — Thu, 20 Aug 2020 05:33:18 GMT

Hello IsaacAsher ,

I have mocked up my version of data:

Used following measure:

Annual Total = VAR _CurrentDate = MAX('Table'[Date]) VAR _Year = YEAR(_CurrentDate) VAR _AnnualTotal = CALCULATE( SUM('Table'[Sales]), YEAR('Table'[Date]) = _Year ) RETURN _AnnualTotal

Result:

If you need to understand the use of variables in DAX, you may read:

https://www.vivran.in/post/dax-using-variables

Ususally, we should have a date table in the model and then we can apply Time Intelligence functions.

For more details on Time Intelligence functions, you may refer to the following article:

https://www.vivran.in/post/dax-time-intelligence-part-2-till-date-aggregations

Hope this helps!

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

Re: Annual Total for Each Item

Anonymous — Thu, 20 Aug 2020 05:11:32 GMT

Hi IsaacAsher

Column

YEAR = YEAR('Table'[Date])

Measures

Total Ann = CALCULATE(SUM('Table'[Saless]), ALLEXCEPT('Table','Table'[YEAR]))

You can also use the measure without creating a Column

Annual Total =

VAR _CurrentDate = MAX('Table'[Date])
var _year = YEAR(_CurrentDate)

RETURN
SUMX(FILTER(ALL('Table'), YEAR('Table'[Date]) = _year),'Table'[Saless])

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Annual Total for Each Item

Anonymous — Thu, 20 Aug 2020 08:49:08 GMT

The best and fastest way to do this calculation is by using Power Query. If you want to add a column to the table with the annual total, you can do it but it will not be as fast and efficient as in Power Query. Here's the formula to get you the annual total in the column:

[Annual Total] = // calculated column var __year = year( T[Date] ) // T is your table var __start = date( __year, 1, 1 ) var __end = date( __year + 1, 1, 1) return SUMX( filter( T, __start <= T[Date] && T[Date] < __end ), T[Sales] )

Re: Annual Total for Each Item

IsaacAsher — Thu, 20 Aug 2020 13:09:38 GMT

Thank you so much! This is exactly the answer I was looking for. Thank you so kindly! vivran22