cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcornfield
Helper III
Helper III

DAX calculate sum of current year.

Hello, 

 

I am trying to Create a measure in DAX that only looks at the Current Year Sales. I can do this with This Formula

Total Rev 2020 = CALCULATE(SUM(SalesAnalytics[Revenue Net]),'Date Table'[Year]="2020")
 
But I don't want to have to change the year every year that passes.
 
I read about this formula that I tried:
Total Rev CY = CALCULATE(SUM(SalesAnalytics[Revenue Net]), Year('Date Table'[Date])=YEAR(Today()))
 
But it repeats the Sum over all the months as you can see in this screenshot.
 
 
 

 

 

1 ACCEPTED SOLUTION
darentengmfs
Post Prodigy
Post Prodigy

Hi @mcornfield 

 

You probably can just apply a relative date filter on the measure.

 

Total Rev = SUM(SalesAnalytics[Revenue Net])

 

Put [Total Rev] in a visual and put a date filter on the visual with Relative Date > in in this > Year

 

 

Alternatively, you can try creating a YTD measure: YTD Sales = CALCULATE(SUM(SalesAnalyticspRevenue Net]), DATESYTD('DateTable'[Date],"12/31"))

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Create a Year slicer from the Date Table and select 2020 there.  Then just use this measure

=SUM(SalesAnalytics[Revenue Net])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@mcornfield , best it use time intelligence with date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Year Rank = RANKX(all('Date'),'Date'[Year Start date],,ASC,Dense)	
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])))
Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. 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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
darentengmfs
Post Prodigy
Post Prodigy

Hi @mcornfield 

 

You probably can just apply a relative date filter on the measure.

 

Total Rev = SUM(SalesAnalytics[Revenue Net])

 

Put [Total Rev] in a visual and put a date filter on the visual with Relative Date > in in this > Year

 

 

Alternatively, you can try creating a YTD measure: YTD Sales = CALCULATE(SUM(SalesAnalyticspRevenue Net]), DATESYTD('DateTable'[Date],"12/31"))

Fowmy
Super User
Super User

@mcornfield 

Where is the screenshot?
can you attache it?

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors