YTD Last Year no showing correctly - no data in date range

[SimonBalzer]

I've just discovered the world of DAX and I'm excited at the possibilities it can offer, but at the same time frustrated as I feel I know so little compared to my knowledge and skills in excel. Anyway to my problem.

I'm trying to create a measure to use in a power pivot (excel 365) that with return total sales YTD LY up to the 8th October (1/1/20 - 7/10/20). I have been able to successfully create the YTD measure but the YTD LY measure is returning the incorrect figure. I have deduced where the problem lies, but I do not know how to resolve it.

The pivot table I'm using the measure in contains several filters, channel, State, product category, sub category etc..... When using say only the State and Channel filter I get the correct YTD LY figures. I believe this is because there is enough sales transaction data at this aggreagated level to create contiguous sales data dates. However once I adding all filters and get down to the product level the transactions are not on every day and therefore the sales data dates are no longer contiguous. This results in the YTD LY figure showing a total to the end of September not to the 7th of October. I can see from the daily data at this level there are sales on the 2nd and 6th of October but nothing on the 8th October, which is the date vlaue the measure is looking for.

This is the measure I'm using 

YTD LY Trimmed (non contiguous):=var lastrelevantdate = lastnonblank('Calendar'[Date],[Total Sales]) return calculate(sum('Inv Sales Data'[Total ex-GST]),dateadd(filter(datesytd('Calendar'[Date]),'Calendar'[Date]<=lastrelevantdate),-1,year))

I have setup a contiguous calendar with the correct structure.

Interestingly if I add in the pivot filters as filters in the measure above I get the correct total.

Hopefully I've been clear in the explanation of my problem and given you enough information to work with. 

[amitchandak]

@SimonBalzer , You need to try measure like these with date table

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

[SimonBalzer]

@amitchandak I have tried your suggestion and it doesn't work.

The result gives me full year last year not to the 7th of October.

[Anonymous]

Hi @SimonBalzer ,

Please try to update the formula of measure [YTD LY Trimmed (non contiguous)] as below and check whether it can get your expected result:

YTD LY Trimmed (non contiguous) =
TOTALYTD (
    SUM ( 'Inv Sales Data'[Total ex-GST] ),
    DATEADD ( 'Calendar'[Date], -12, MONTH )
)

In addition, you can refer the content in the following links to get it.

Get the YTD of the same period last year

Same Period Last Year to Date DAX Calculation in Power BI

Best Regards

[SimonBalzer · Accepted solution]

@Yi thank you for the reply any your formula. Unfortunately your suggestion did not work.

However this link you gave me;

Same Period Last Year to Date DAX Calculation in Power BI

did provide a formula, which solved the issue. The final soluton was

Measure 13:=var lastdateAvailable=CALCULATE(MAX('Inv Sales Data'[Transaction Date]),ALL('Inv Sales Data'))

var lastyearsameday=lastdateAvailable-365

var ifLY=IF(DAY(lastyearsameday)<>DAY(lastdateAvailable),TRUE(),FALSE())

var lastyearsamedayLY=IF(ifLY,lastdateAvailable-366,lastyearsameday)

var SPLYUntillastdate=FILTER(

SAMEPERIODLASTYEAR('Calendar'[Date]),

'Calendar'[Date]<=lastyearsamedayLY)

return

CALCULATE(

[Total Sales],

SPLYUntillastdate)