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
Super UserAre you using a slicer with the Year column from your calandar (date) table?
Nope, there are no slicers.
You need some kind of slicer or other input to give it a date context. If you don't tell it which year, it will just give you everything. DATESYTD doesn't give you the dates year to date of this year, it gives you all the dates year to date of whatever date context it's plotted against. If you want it to give you the current year's year to date values for today's date, you need to give it that date.
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.