Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

PYTD Formula Issues

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! 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    v-kelly-msft
    5 years ago

    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!

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!