Forum Discussion

LisaB's avatar
LisaB
Helper 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())                        ...
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

LisaB's avatar
LisaB
Helper III
7 years ago

Hi Anonymous,

 

Thanks - that formula gives me a value now. However, not the value I'm looking for.

 

In my PostedSalesInvoice table, I have two dates, posting date and order date. It might be that an invoice has been posted after the order date, i.e. order date = 2018-01-29 but posting date = 2018-01-31.

 

I want to show the values filtered on order date = same date LY. Now it gives me the value of posting date = same date LY.

 

Hope this makes sense.

 

Lisa

Anonymous's avatar
Anonymous
Not applicable
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

  • AsilAllaham's avatar
    AsilAllaham
    Frequent Visitor
    7 years ago

    Hi @Chiara,,

    Thanks for your solution it helped me a lot in a similar case that needed SameDayLY calculated column but your way returned a true value for "2019-03-01" if Today date was a Leap day for example "2020-02-29" but that was a wrong behavior according to my business requirements so I handled it as follow:

    SameDayLY =
    var TodayDate = TODAY() // "2020-02-29"
    var TodayYear = YEAR(TodayDate)
    var LastYear = YEAR(TodayDate)-1
    var LastMonth = MONTH(TodayDate)
    var LastDay = DAY(TodayDate)
    return
    IF ( MOD(TodayYear,4) = 0 && LastMonth = 2 && LastDay =29 , FALSE() , 'Calendar'[Date]= DATE(LastYear,LastMonth,LastDay))

    Regards;