cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hmichael8
Helper II
Helper II

How to calculate proper YTD across multiple years

Hi,

 

I have a data table which consists of records for over 3 years. I am using custom years where the financial year starts in Apr and ends in March. I have a measure which sums the column (traffic) and I would like to create a visual where I can see the traffic figures for each entity over several years (like the visual below).

Annotation 2020-04-16 102614.png

The issue I am having is, as 2021 is not a complete year, the results showing are hard to compare. I want the traffic to be calculated to date. Meaning, I would like 2019, 2020 and 2021 traffic to be displaying for the exact same date range except one year prior. So, if the last date for traffic in my data is 16th April 2020 I want;
- 2019 to be between 1st Apr 2018 - 16th Apr 2018
- 2020 to be between 1st Apr 2019 - 16th Apr 2019
- 2021 to be between 1st Apr 2020 - 16th Apr 2020

 

The graph is currently set up with name, month and weeknum in the axis, year in legend, and total traffic in value.

 

Thanks  

2 REPLIES 2
amitchandak
Super User IV
Super User IV

@hmichael8 

In case you have selected 16 April 2020 on-page. You can try totalYTD or datesytd

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"3/31"))

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@hmichael8 ,

You can also try


YTD Sales =
var _max = today()
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"),'Date'[Date]<= _max)


Last YTD Sales =
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"),'Date'[Date]<= _max)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors