Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.
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!
Solved! Go to Solution.
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
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]
)
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
Thank you so much! This is exactly the answer I was looking for. Thank you so kindly! @vivran22
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)
Thank you @harshnathani
Unfortunately when I tried this trick, it gave me the same results in the measure (Ann Total) as the Sales column.
Hi @IsaacAsher
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)
User | Count |
---|---|
10 | |
8 | |
5 | |
5 | |
4 |