DATESYTD / time intelligence not working in custom column

[Anonymous]

I apologize that I can't share the actual pbix file - I'll do my best to describe the problem.

I created a calendar table with a relationship to a CreatedOn .   Both are in a common 'Date' format - i.e. 1/1/2017.

However, time intelligence functions do not work at all.   I want to see the count of records with date YTD, and I have the following expressoin:

Custom Col = CALCULATE ( COUNTA(NameOfSomething), DATESYTD('Calendar'[Date]) )

Yet I am seeing ALL records.   I get the exact same results with no DATESYTD filter applied at all.

Any common reasons why this might occur?   I can provide more details if necessary.   Thanks again!

[BhaveshPatel]

Are you creating a measure. 

This formula works in the measure calculation. This measure then dragged and dropped in the table visualization to get your desired output.

[Anonymous]

Bhavesh - I tried first as a custom column, and it shows 'everthing' (does not filter).   When I try as a measure, I get no results (completely null/blank).

[blopez11]

Are you using a slicer with the Year column from your calandar (date) table?

[Anonymous]

Nope, there are no slicers.

[KHorseman]

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.

[Anonymous]

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.

[KHorseman]

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.

[Anonymous]

I 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.

[v-qiuyu-msft · Accepted solution]

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

[Anonymous]

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?

[Anonymous]

The context is really driven by the 'CreatedOn' field in that dataset.

[KHorseman]

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.

[Anonymous]

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.

[KHorseman]

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

[mpsrshl]

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.

[Anonymous]

I should note I have this data entered into a matrix table and I'm grouping it 'by person' associated with these records.