YTD vs Last Year YTD

[Harry_Tran]

Hi everyone,

I need to compare TotalSale YTD vs TotalSale Last Year YTD.

For exmple, if today is Jan 18 2022, then I would like to compare TotalSale this year from 1/1/2022 to 1/17/2022 to TotalSale Last Year from 1/1/2021 to 1/17/2021.

My mesure are:

TotalSale YTD = TOTALYTD ([Totalsale], DATESYTD (CALENDAR[Date])

TotalSale LY YTD = CALCULATE ([TotalSale YTD], SAMEPERIODLASTYEAR (CALENDAR[Date])

Thank you

The issue that I have is when I apply these measures above to te metric (regions as rows and date as columns) , the TotalSale LY YTD would be the Sum of whole last year and TotalSale YTD shows the right number for current year but it shows the total for last year

[v-yangliu-msft · Accepted solution]

Hi  @Harry_Tran ,

I created some data:

Here are the steps you can follow：

1. Create measure.

TotalSale YTD =
CALCULATE(SUM('Table'[rand]),FILTER(ALL('Table'),'Table'[Date]>DATE(YEAR(TODAY()),1,1)&&'Table'[Date]<TODAY()))

TotalSale LY YTD =
CALCULATE(SUM('Table'[rand]),FILTER(ALL('Table'),'Table'[Date]>DATE(YEAR(TODAY())-1,1,1)&&'Table'[Date]<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

ENsure that the Calendar Table goes only till today, not till December 31, 2022.

[OwenAuger]

Hi @Harry_Tran 

I suggest you follow the approach discussed in this article:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

The short description is:

1. Add a DatesWithSales column to your CALENDAR TABLE (adjust column names as required):

DatesWithSales =
    'CALENDAR'[Date] <= MAX ( Sales[Date] )

 2. Rewrite any time intelligence measures in this sort of form, using TotalSale LY YTD as an example:

(I have put all the date shifting in one measure rather than referencing TotalSale LY)

TotalSale LY YTD =
CALCULATE (
    [Totalsale],
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( DATESYTD ( 'CALENDAR'[Date] ) ),
        'CALENDAR'[DatesWithSales] = TRUE
    )
)

Does this work for you? 

Regards,

Owen