Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes! Register now.

Reply
IsaacAsher
Helper I
Helper I

Annual Total for Each Item

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!

 

DummyData.png

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

Hello @IsaacAsher ,

 

I have mocked up my version of data:

vivran22_0-1597893197935.png

 

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:

vivran22_1-1597893279767.png

 

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

 

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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]
    )

 

 

vivran22
Community Champion
Community Champion

Hello @IsaacAsher ,

 

I have mocked up my version of data:

vivran22_0-1597893197935.png

 

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:

vivran22_1-1597893279767.png

 

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 

harshnathani
Community Champion
Community Champion

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

 

1.jpg

 

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])

 

1.jpg

 

 

 

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

Helpful resources

Announcements
September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.