Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DATESYTD / time intelligence not working in custom column

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...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
    mpsrshl
    Icon for Advocate II rankAdvocate II
    7 years ago

    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.