PYTD Formula Issues

[Anonymous]

Hi All,

My DAX for PYTD is giving me issues. I have to change the day (e.g. 372 at end of formula) every few months because the number of selling days is different this year from last year. I'd like a permanent solution if possible.

PYTD = CALCULATE(SUM('AssaySales'[Extended Sales PMAR Amount]),FILTER(ALL('Date'),AND('Date'[Date]>=01/01/2020,'Date'[Date]<=TODAY()-372)))

Is there a way to use the SAMEPERIODLASTYEAR function?

Thanks! 

[amitchandak · Accepted solution]

@Anonymous , you can try measures like

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"))

or like these

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd("Date"[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR("Date"[Date]))

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

If your dates are contiguous,you could use "DATEADD" or "SAMEPERIODLASTYEAR",otherwise create a date table first.

Then you could use below fomular:

PYTD = CALCULATE(SUM('Table'[Value]),SAMEPERIODLASTYEAR('Table'[Date]))

Or:

PYTD1 = CALCULATE(SUM('Table'[Value]),DATEADD('Table'[Date],-1,YEAR))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[amitchandak · Accepted solution]

@Anonymous , you can try measures like

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"))

or like these

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd("Date"[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR("Date"[Date]))