cancel
Showing results for
Did you mean:  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). 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  Super User IV

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"))
``````

Proud to be a Super User!  Super User IV

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

Proud to be a Super User!  Announcements #### Welcome to the User Group Public Preview  