Forum Discussion
DATESYTD / time intelligence not working in custom column
Hi Anonymous,
DATESYTD() function is used for returning a table that contains a column of the dates for the year to date, in the current context. When you create a measure like below:
=CALCULATE ( COUNTA(NameOfSomething), DATESYTD('Calendar'[Date]) )
And place this measure in a table or matrix, it will return running count values for the year to date. In my sample, I write the measure like below:
Measure = CALCULATE(COUNTA('SalesFact'[ProductID]),DATESYTD('Date'[Date]))
1. The actual count of ProductID for each date is below.
2. When we put the measure into the table, you can see the measure returns running count values until 12-31 then reset. (As we don't set end date of year in DATESYTD() function, the default is December 31)
Best Regards,
Qiuyun Yu
Interesting. I'd prefer not to have manual input from the user - this is a "scoreboard" type report displayed on a large TV.
Rather than hard-coding it, is there a way to limit it to true YTD? I will also be doing prior year to date columns.
No I don't mean manual input, I mean the design of the report. Your report has to have some kind of context. Especially if you're doing the prior year's year to date, presumably you would have something on your report that shows that actual year. I don't know what kind of visual you're using but for instance let's say you're making a bar chart. You would normally make the x-axis a month/year column from your date table, so you'd see each month's year to date total. Or a quarter or week or whatever. You could even use your basic date column and Power BI will automatically give you a year - quarter - month - date hierarchy.
v-qiuyu-msftCommunity Support
Hi Anonymous,
DATESYTD() function is used for returning a table that contains a column of the dates for the year to date, in the current context. When you create a measure like below:
=CALCULATE ( COUNTA(NameOfSomething), DATESYTD('Calendar'[Date]) )
And place this measure in a table or matrix, it will return running count values for the year to date. In my sample, I write the measure like below:
Measure = CALCULATE(COUNTA('SalesFact'[ProductID]),DATESYTD('Date'[Date]))
1. The actual count of ProductID for each date is below.
2. When we put the measure into the table, you can see the measure returns running count values until 12-31 then reset. (As we don't set end date of year in DATESYTD() function, the default is December 31)
Best Regards,
Qiuyun YuI see what you mean. In my case I am displaying info in a matrix table, but I am not displaying any sort of date info other than 'current ytd' and 'prior ytd' in the field names.
The context is really driven by the 'CreatedOn' field in that dataset.
No it isn't. That's not how context works. The CreatedOn date isn't the context, it's the thing the context acts on. The measure compares the CreatedOn date against whatever context you feed it. If you feed it nothing, it will give you everything.
Think of everything you put on a report page as a filter. If you make a chart, the x-axis is a variable filter. In my month example, each month on the bar chart is a little mini-filter on your measure; each month on the chart tells DATESYTD the end date for which it should return all dates year-to-date.
Shouldn't DATESYTD be smart enough to know what 'YTD' means though? I'm not sure how I'd provide context in a matrix table.
DATESYTD isn't DATESUNTILLITERALLYTODAY. I think you're misunderstanding its purpose. It's smart enough to work on any date context you give it. If you want a card that always shows year-to-date for today, there are a few ways to do it. In my opinion the most flexible method is to add a DayDiff column to your date table.
DayDiff = INT(DateTable[Date] - TODAY())
Then you can add a visual level filter to your card: DateTable[DayDiff] = 0
Qiuyun - Thank you! That illustration helped me understand. So if I want to do current year and prior year using that function, would I need two separate calendar tables, one containing only the dates for each year?
mpsrshlAdvocate II
thanks for this tip! was super scratching my head around solving this using mere formulas rather than filtering the visual by todays and less values - for a dashboard that shows not only past and present but future measures.