Forum Discussion

LisaB's avatar
LisaB
Icon for Helper III rankHelper III
7 years ago
Solved

Today compared to same day last year

Hi everyone,

 

I have a measure showing order value for today:

 

OrderValueToday = CALCULATE(SUM(PostedSalesInvoices[Amount]);PostedSalesInvoices[Order_Date] = TODAY())
                                  +
                                  CALCULATE(SUM(SalesOrderList[Amount]);SalesOrderList[Order_Date] = TODAY())
 
I wish to show a measure showing the order value for same day last year. For this I will only have one transaction table [PostedSalesInvoice] and I wish to filter on order date last year. I have tried with SAMEPERIODLASTYEAR and PREVIOUSYEAR (+ filter on today's date) without any success. 
 
Any suggestions?
 
Thanks!
 
Lisa
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi LisaB,

     

    I suppose that PostedSalesInvoice table is connected to the calendar table by posting date. You can either change the relationship by connecting to the order date or you can create a new inactive relationship between calendar date and order date.

    Then you can write a measure as below:

     

    OrderLY =
CALCULATE (
    CALCULATE (
        SUM ( PostedSalesInvoices[Amount] ),
        USERELATIONSHIP ( PostedSalesInvoices[Order_Date], CalendarDate[Date] )
    )
        + SUM ( SalesOrderList[Amount] ),
    SAMEPERIODLASTYEAR ( CalendarDate[Date] ),
    CalendarDate[SameDayLY] = TRUE ()
)

    I hope you can solve now!

     

    Chiara

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi LisaB

     

    Do you have Calendar table in your Model? If not, please create a calendar table and then you can try this DAX:

     

    LastYear Numbers = CALCULATE(SUM(Sales_Fact.Sales),DATEADD(Dates[Date],-1,YEAR)

     

    Thanks
    Raj

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi LisaB,

     

    You can create a calculate column in Calendar Dimension:

     

    SameDayLY= 
var TodayDate = TODAY()
var LastYear = YEAR(TodayDate)-1
var LastMonth = MONTH(TodayDate)
var LastDay = DAY(TodayDate)
return
'Calendar'[Date]= DATE(LastYear,LastMonth,LastDay)

    Then a measure:

     

    OrderValueSameDayLY = CALCULATE(SUM(PostedSalesInvoices[Amount]);CalendarDate[SameDayLY]=TRUE()) 
                                  +
                                  CALCULATE(SUM(SalesOrderList[Amount]);CalendarDate[SameDayLY]=TRUE())

    Regards

    Chiara

     

     

    • LisaB's avatar
      LisaB
      Icon for Helper III rankHelper III
      7 years ago

       Hi Anonymous and Anonymous,

       

      Thank you for your replies.

       

      I've got a calendar table.

       

      With the first formula (rejendran) I get the value for the whole month last year. How can I add the filter on order date = samedayLY?

       

      With the second formula I didn't get anything at all. :/

       

      Worth to mention is that I have a report level filter date on this month.

       

      Thanks!

       

      Lisa

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        After calculate column samedayLY try this measure:

         

        OrderLY= CALCULATE[OrderValue], SAMEPERIODLASTYEAR(CalendarDate[Date]), CalendarDate[samedayLY] = TRUE())