Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hi all,
I have a dataset with about 7 years of data. I also have a measure that only shows the sales for the latest year in the dataset. I need to add measures that will show the sales of each prior year to date. So for example, as of today, 2019's data should show from 2019-01-01 to 2019-06-14.
I'm using the DATEADD function, but in order for this to work properly, the date column that I parse to it needs to be filtered to this year first, before subtracting a year to show the same timeframe in 2019, otherwise it shows all the prior years.
So my question is, how do I filter a date column before it gets parsed to the DATEADD function?
I realize that manually selecting the latest year in a slicer will fix this problem, but the idea is for this to show each year's data correctly without having to select the latest year first.
Thanks!
This is commonly referred to as "Year Over Year To Date" or YoYTD
For this to work you need to add a calculated column to your Dates table that flags all dates in prior years as either being equal to or
before the current date, or being after the current date.
Once you have that flag you incorporate it into the DATEADD formula (or whatever you prefer)
Like so:
SalesPYTD = calculate (sum(sales),SAMEPERIODLASTYEAR(dates[date]),flag = TRUE())
CAVEAT! (there's always a caveat, innit?) The weekday structure (and where holidays fall) does not match between years, so you are invariably comparing apples to oranges. Very similar ones, but still apples and oranges.
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
24 | |
19 | |
14 | |
10 | |
7 |