Calendar table for YTD and LYTD calculations

[Anonymous]

Hi,

I am trying to implement YTD and LYTD, I have fact table which has invoicedate. Do I still need to create Date Calendar table?

I used YTD calculation as like below:

YTD  = TOTALYTD(SUM('Sales Objects'[Sales]),'Sales Objects'[invoicedate])  Sales has data for till today and this formula works fine, but I have forecast as well till this year end.

YTD  = TOTALYTD(SUM('Sales Objects'[Forecast]),'Sales Objects'[invoicedate]) -- When I use this formula its calculating entire year, it supposed to do till today.

Not sure why its differentiates between Sales and Forecast.

Thanks,

[Greg_Deckler]

I would definitely use a separate calendar table and relate your SalesObjects table to it on InvoiceDate. That's how the time intelligence functions were designed to work. There may be something weird going on with the context or something not having a separate date table.

[Anonymous]

Forecast values are not correct using this formula, still its calculating entire year, not the Year to Day forecast.

YTD = TOTALYTD(SUM('Sales Objects'[Forecast]),'Sales Objects'[invoicedate])
YTD = TOTALYTD(SUM('Sales Objects'[Forecast]),'DateKey'[Date])

Not sure where is the mistake. any help

Thanks,

[mattbrice]

Do you have a filter/slicer or month or date on rows / columns somewhere in your visual (from Calendar table)?  TOTALYTD internally converts the DAX to the logical equivalent of this and needs to figure out max value in current filter context:

=
CALCULATE (
    SUM ( 'Sales Objects'[Forecast] ),
    FILTER (
        ALL ( 'DateKey' ),
        'DateKey'[Date] <= MAX ( 'DateKey'[Date] )
            && YEAR ( 'DateKey'[Date] ) = YEAR ( MAX ( 'DateKey'[Date] ) )
    )
)

[Anonymous]

No, but how this matters as long as formula is bringing right results. I still see the same issue with your fomula as well.

Thanks,

[mattbrice · Accepted solution]

Put another way, the formula needs to be told what "Today" is to calculate YTD.  If you don't "tell" it, it will use the max value in your calendar table to compute the YTD value.

[Anonymous]

I did it exactly and looks working...

[Anonymous]

I checked without date calendar table and used invoice date from Sales Objects and it is showing correct results. Just wanted to understand what is the point to bring calendar table?

[v-huizhn-msft]

Hi @Anonymous,

Usually, if you use the 'Sales Objects'[invoicedate], it easy to effect by row context and filter context by the table itself. For some intelligence function, which need the continuous date. If you use separate calendar table, it have continuous date without context.

More details for DAX context, plesae review this article.

In addition, please mark the right reply as answer, and welcome to share your workaround, more people will benefit from it.

Thanks,
Aneglia