Forum Discussion

heathernicole's avatar
heathernicole
Icon for Continued Contributor rankContinued Contributor
8 years ago
Solved

SAMPLEPERIODLASTYEAR pulling FULL year - not previous YTD

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. 

Need Help
  • heathernicole's avatar
    heathernicole
    8 years ago

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

10 Replies

  • SivaMani's avatar
    SivaMani
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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's avatar
      heathernicole
      Icon for Continued Contributor rankContinued Contributor
      8 years ago

      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's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

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