Previous Date value by country and product and with missing days

[JanLofgren]

Hi Guys,

I have following table

And I'm looking to retrieve the previous value for the date before (attention, list does not contain all days as the weekend or Holidays are not included) for the same product and for the same country.

I tried already a measure with EARLIER but due to the missing days this is not working. 

Do you have any idea how I could solve this ?

Thanks in advance already,

Jan

[Zubair_Muhammad · Accepted solution]

@JanLofgren

Try this calculated column

Column =
VAR currentdate = TableName[Date]
VAR PreviousDate =
    CALCULATE (
        MAX ( TableName[Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Country], TableName[Product] ),
            TableName[Date] < currentdate
        )
    )
RETURN
    CALCULATE (
        SUM ( TableName[Value] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Country], TableName[Product] ),
            TableName[Date] = PreviousDate
        )
    )