SAMPLEPERIODLASTYEAR pulling FULL year - not previous YTD

[heathernicole]

I am trying to compare YTD sales to Previous YTD sales. 

YTD formula:

SalesYTD = 
CALCULATE (
[Incoming Total Sales], 
FILTER (
ALL ( 'Calendar - Transaction Date' ), 
'Calendar - Transaction Date'[Transaction Date Year Number] = MAX ( 'Calendar - Transaction Date'[Transaction Date Year Number] )
&& 'Calendar - Transaction Date'[Transaction Date] <= MAX ( 'Calendar - Transaction Date'[Transaction Date])
)
)

PYTD Sales = 
CALCULATE (
    [Incoming Total Sales], 
    FILTER (
        ALL ( 'Calendar - Transaction Date' ), 
        'Calendar - Transaction Date'[Transaction Date Year Number] = (MAX ( 'Calendar - Transaction Date'[Transaction Date Year Number]) -1)
            && 'Calendar - Transaction Date'[Transaction Date] <= MAX ( 'Calendar - Transaction Date'[PY Date])
    )
)

However - the PYTD measure (no matter what variation I do) is pulling the FULL year for last year

Even using SAMPLEPERIODLASTYEAR. I can NOT figure out why it's doing that rather than pulling JUST the data for YTD LAST year. 

Previous Year Sales (Incoming) = CALCULATE([Incoming Total Sales], SAMEPERIODLASTYEAR('Calendar - Transaction Date'[Transaction Date]))

ANY ideas as to WHY it's behaving this way? It's the same on EVERY report I have. 

[SivaMani]

@heathernicole,

Why don't you try this formula,

YTD = TOTALYTD(SUM([Income Total Sales]),'Calendar - Transaction Date')

PYTD = CALCULATE([YTD],SAMEPERIODLASTYEAR('Calendar - Transaction Date') )

[heathernicole]

@SivaMani - thanks for your input, first off. 🙂 

Here are the results - I've tried this variation before and I get the same results no matter what. 

[Zubair_Muhammad]

Hi @heathernicole

Give this a shot. In PYTD sales MEASURE

Replace

MAX ( 'Calendar - Transaction Date'[PY Date])

With

If(HASONEVALUE(
'Calendar - Transaction Date'[Date]),
DATEADD(values('Calendar - Transaction Date'[Date]),-1,YEAR))

[heathernicole]

@Zubair_Muhammad - Thank you for your help! 🙂 

It only produced a blank value. 😞 

[parry2k]

@heathernicole do you have calendar/date dimension in your model? If not you need one, and then it will be easier to work with.

[heathernicole]

@parry2k - I do! That's why I'm not sure why it's having so many problems...

[heathernicole · Accepted solution]

https://community.powerbi.com/t5/Desktop/Prior-year-year-to-date-sales/m-p/173509#M75776 

So the solution from this forum question did the trick perfectly... it doesn't quite answer why the other methods didn't work - but for now, it gives the data I need.

here's the formula modified; it's quite a bear of a formula: lol 

PYTD Sales = 
CALCULATE(
[Incoming Total Sales],
SAMEPERIODLASTYEAR(
DATESYTD('Calendar - Transaction Date'[Transaction Date])
),
FILTER(
ALL('Calendar - Transaction Date'),
'Calendar - Transaction Date'[Transaction Date Month Number] <= (MONTH(NOW())-1)
)
)+
CALCULATE(
[Incoming Total Sales],
SAMEPERIODLASTYEAR(
DATESYTD('Calendar - Transaction Date'[Transaction Date])
),
FILTER(
ALL('Calendar - Transaction Date'),
'Calendar - Transaction Date'[Transaction Date Month Number] = MONTH(NOW())
&& 'Calendar - Transaction Date'[Transaction Date Day Number] <= DAY(NOW())
)
)

[parry2k]

@heathernicole thanks for sharing, it seems way too much for a simple calclulation and i did it many times in the past with similar fomulas proposed to your earlier and it worked fine, something we are surely missing in your data model.

Anyhow, atleast you can move on and get back to this when you have time. 🙂

[heathernicole]

@parry2k - That's what I thought - I'm not sure what I'm missing in the data model either, but for now it will work. 🙂 

Thanks for your help!

Also, @Zubair_Muhammad @SivaMani 

[parry2k]

@heathernicole it suppose to solve the problem. Interesting, it should work.

Can you share pick of relationship model, anything weird there.