Forum Discussion

minimynt's avatar
minimynt
Frequent Visitor
10 years ago
Solved

DAX Last Year Sales

Hi,

 

I want to create LY Sales calculation, but somehow there's something wrong with the result.

The total of LY Sales is right, but I don't have any idea why LY sales for each year is not showing.

Here is my formula

LY Sales = CALCULATE([Total Sales], DATEADD(Orders[Order Date],-1,YEAR))

Does anybody know why? :(

 

 

Thanks,

Priscilla

Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    Hello, try this instead: LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Orders'[Order Date]))

     

    If you have a dedicated date table, you'll want to use the date column from there instead of Order Date

  • minimynt's avatar
    minimynt
    10 years ago

    Anonymous Hi, I've also tried with SAMEPERIODLASTYEAR but the result is the same. However, I just figured out that only continuous date is valid for this case and my OrderDate isn't continuous so that's why.

     

    Thanks anyway! :)

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hello, try this instead: LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Orders'[Order Date]))

     

    If you have a dedicated date table, you'll want to use the date column from there instead of Order Date

    • minimynt's avatar
      minimynt
      Frequent Visitor
      10 years ago

      Anonymous Hi, I've also tried with SAMEPERIODLASTYEAR but the result is the same. However, I just figured out that only continuous date is valid for this case and my OrderDate isn't continuous so that's why.

       

      Thanks anyway! :)

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Guys, 

        I know it's quite irritating to get the prior year meaures. I tried looking everywhere left with no choice than to try all by myself. Please find the solution below 

        LY Sales = CALCULATE([Total Sales],Calender[Year] = YEAR(TODAY()) - 1)

        Total Sales = Your measure 
        Calender = Your date table
        I am just subtracting 1 from the current years number in this expression YEAR(TODAY()) - 1). 
        Please accept it as a solution if it resolved your issue
  • sure19's avatar
    sure19
    Helper II
    9 years ago

    Hi,

    first i have filtered year, then inserted region and sales, when i try to use the above to calculate last year sales, it gives me blank.  is it because i have already filtered the year?  how can i still show last year sales against this year sales?

     

    thanks in advance

     

    Suresh

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Is your date column continuous? If you're using a seperate date table, did you create the relationship using 2 fields with a Date data type?

    • vicks's avatar
      vicks
      Frequent Visitor
      8 years ago

      Hi Suresh;

       

      Did you solved your problem?, i have same issue about that, the formula didnt worked when i filtered by Year;

      i have single fact table with sales measure, person dimension, Year, Month, Date(dateSQL) Dimension;

       

      warm regards;

       

      Opik