Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I'm struggling to figure out how to do a bespoke YTD calculation, essentially what we are trying to achieve is that for any given filter year, display the equivalent YTD for that year.
For examples:
2015 | 2016 | 2017 | 2018 | 2019 | ||||||
Turnover £ | Turnover YTD £ | Turnover £ | Turnover YTD £ | Turnover £ | Turnover YTD £ | Turnover £ | Turnover YTD £ | Turnover £ | Turnover YTD £ | |
Product 1 | 100 | 50 | 200 | 100 | 150 | 75 | 300 | 150 | 350 | 350 |
Product 2 | 40 | 20 | 50 | 25 | 100 | 50 | 180 | 90 | 200 | 200 |
Product 3 | 200 | 200 | 300 | 225 | 400 | 280 | 800 | 750 | 1000 | 1000 |
So for the current year the Turnover £ and YTD £ will be the same, but for previous years it will be the equivalent time period for the filter year.
Here are the current measures:
Turnover £:=Orders[Order Value]-Credits[Credit Value]
I think I need my YTD measure to be something like CALCULATE(Orders[Turnover £], Dates[DayofYear] <= lastdate(Orders[Invoice Date])) But obviously this isn't how it is done.
Does anyone have any pointers please?
Thanks you in advance,
Alister
Hey,
I recommend to start with this article, almost everything timerelated is covered here: https://www.daxpatterns.com/time-patterns/
Regards,Tom
Hi Tom,
Thanks for the response. I've read through that before and it is a great article. What I found there only worked for the current year, but when another year was the filter, the measure displayed all of that year, instead of the equivalent YTD - I maybe should have changed the title of this post.
I'll have another read and see what I can figure out. I was hoping that this was a common one with an obvious answer I am missing.
Many thanks,
Alister
Hi there.
Mate, if you say YTD, it's obvious that the total value of any measure for the whole year will be equal to the YTD (of the measure in question) for the same year. This follows from the very definition of YTD. So, either you want to calculate something different than YTD or... you should give us a clear example of what it is you want.
Otherwise, we'll be chasing our tail, so to speak.
Best
Darek
Hi Darek,
I probably could have explained it better, but I don't know the term _equivalent YTD period_. So the equivalent time period that would be YTD this year, 1st Jan to 3rd July (at time of posting), for ny selected year.
The following is almost there:
CALCULATE ( [Turnover £], DATESBETWEEN ( Dates[Date], FIRSTDATE(Dates[Date]), DATE(YeaR(max(Dates[Date])),month(today()),day(today())) ) )
I'm now working on trying to amend the dateto in the datesbetween function to be using a constant date based on the lastdate of another datefield. CALCULATE(LASTDATE(Orders[Invoice Date]), ALL....)) but it isn't working quite right yet.
Many thanks,
Alister
I guess you want something like this:
[YTD with TODAY shifted as upper limit] :=
var __today = TODAY()
var __todaysYear = YEAR( __today )
var __visibleYear = SELECTEDVALUE( 'Dates'[Year] )
var __oneYearVisible = HASONEVALUE( 'Dates'[Year] )
var __numberOfYearsBackFromToday = __todaysYear - __visibleYear
var __upperDayLimit = EDATE( __today, -12 * __numberOfYearsBackFromToday )
var __ytd =
CALCULATE(
[My Measure],
DATESYTD( 'Dates'[Date] ),
'Dates'[Date] <= __upperDayLimit
)
return
if( __oneYearVisible, __ytd )
Best
Darek
Thanks, I'll dissect and have a go.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |