Need help with YTD and PY YTD

[Anonymous]

Hi,

I'm trying to set up measures for YTD and PY YTD but have noticed a few inconsitencies when working with weeks. My formulas are as below:

YTD:

YTD Rev = CALCULATE([Revenue],DATESYTD('MD Date Table'[Date],"30/09"))

PY YTD:

PY YTD Rev = CALCULATE ( [YTD Rev], SAMEPERIODLASTYEAR('MD Date Table'[Date]))

The issue:

As I'm using Fiscal Year & Fiscal Year Week as a filter, what appears in one year as the YTD value, doesn't appear in the PY YTD value when the year is pushed forward by one. See below for screenshots:

As you can see - the values are different. However, the week ending date is also different as the days in the week move each year by 1 day. The missing value between the blue highlighted value and the yellow highligted value is from that missing day "3rd January".

Is anyone aware of a way to include this missing day in the PY YTD calculation so that the PY YTD value in 2020 matches that of the YTD value in 2019?

Any help would be much appreciated!

Thanks,

Aaron

[MattAllington]

You can’t use inbuilt time intelligence functions for week calculations. There are various approaches using custom time intelligence. You can read my article here for an overview. 

https://exceleratorbi.com.au/dax-time-intelligence-beginners/

In general, you need a “week number of year” column and a fin year column. You can remove the filter from this year and keep the filters on the week number. 

[Anonymous · Accepted solution]

Resolved the problem with the following syntax:

PY YTD Wk Rev = 
IF (
    HASONEVALUE ( 'MD Date Table'[PD Fin Year] ),
    CALCULATE (
        SUM ( 'Revenue Table'[Revenue] ),
        FILTER (
            ALL ( 'MD Date Table' ),
            'MD Date Table'[PD Fin Year] = VALUES ( 'MD Date Table'[PD Fin Year] ) -1
                && 'MD Date Table'[CD FY Week Number] <= MAX ( 'MD Date Table'[CD FY Week Number] )
        )
    ),
    BLANK ()
)

Thanks for pointing me away from the inbuilt time intelligence solutions!

Aaron

[Anonymous]

Hi Matt - thanks for the speedy response!

Thanks for pointing me in the direction of your article. It was really interesting! Is it true that I can't use inbuilt time intelligence functions even if my data is at a daily level of granularity?

I already have the week number (as per the custom FY calendar) and fiscal year in my master calendar table.

My YTD values come back as I would expect, I just can't seem to get it to replicate in the PY value when I moved the year forward by one - as the same week a year ahead doesn't necessarily contain the exact same days.

EDIT : Would restructuring my calendar help? I need the weeks to be from Friday - Thursday though.

[MattAllington]

---

@Anonymous wrote:

1. Is it true that I can't use inbuilt time intelligence functions even if my data is at a daily level of granularity?

2.  Would restructuring my calendar help? I need the weeks to be from Friday - Thursday though.

---

I am glad you sorted it out.   Regarding the questions from your earlier post.

1. Yes it is true.  The issue is that the inbuilt functions have no concept of a week.  Yes you have the weeknumber in yoru calendar but the inbuilt functions don't know about them.  The fact that your week runs Fri - Thu is case in point - everyone is different.  But all is not lost, just use the custom patter (like you have).

2. Yes, a good calendar table is essential for easily writting custom time intelligence.