Error in MTD LY , QTD LY, YTD LY

[DeepDive]

Hi All,

While using below DAX, I am getting error in Last year calculation for all(like MTD LY, QTD LY and YTD LY).
However Current year (ie. MTD, QTD, YTD) calculations are working fine. When I used Datesbetween for calculating
last year data it gives me correct answer. Pls suggest how to use MTD LY, QTD LY and YTD LY here in this scenario

TotalSales = SUM(Sheet1[Net Sales Value])

TotalSales LY = CALCULATE([TotalSales],DATEADD('Date'[Date],-1,YEAR))

MTD = CALCULATE([TotalSales], DATESMTD('Date'[Date]))

MTD LY = CALCULATE('Date_MeasureTable'[TotalSales LY], DATESMTD('Date'[Date]))

QTD = CALCULATE(Date_MeasureTable[TotalSales], DATESQTD('Date'[Date]))

QTD LY = CALCULATE(Date_MeasureTable[TotalSales LY], DATESQTD('Date'[Date]))

YTD = CALCULATE(Date_MeasureTable[TotalSales], DATESYTD('Date'[Date]))

YTD LY = CALCULATE(Date_MeasureTable[TotalSales LY], DATESYTD('Date'[Date]))

---

[jdbuchanan71]

@DeepDive 

Make sure your date table contains full years from Jan 1st - Dec 31st.  I have seen odd behavior if your date table starts on say July 1st.

[DeepDive]

@jdbuchanan71 am using Calendar(min(Sheet1[Invoice_Date]), max(Sheet[Invoice_Date)).. which creates date calendar for Data captured period only. ( i have data form Jan 2017 to July 2017).... Min date in Calendar is 2-Jan-2017 and Max date till 30th -July-2019.

[jdbuchanan71 · Accepted solution]

Try changing your calendar code to

VAR StartDate = MIN ( Sheet1[Invoice_Date] )
VAR EndDate = MAX ( Sheet1[Invoice_Date] )
RETURN
CALENDAR ( DATE ( YEAR ( StartDate ) , 1 , 1 ), DATE ( YEAR ( EndDate ) , 12 , 31 ) )

This will extend your date table to include the full years.

[Anonymous]

what @jdbuchanan71  and also be sure to set your date table as such: