DAX formula to calculate change from yesterday

[Anonymous]

I have a data set which shows available units by product and day. I would like to add a column which calculates the change in available units for each product from the previous day. What formula would I used to create this column?

[rajendran]

Do you have seperate record for each item for each day? could you please post sample data ( in copy- pastable format) with expected output?

Thanks
Raj

[Anonymous]

Day              SKU        Available count      Daily Change
9/15/2018   111111    20
9/15/2018   111112    17
9/15/2018   111113    14
9/16/2018   111111    18                          2
9/16/2018   111112    16                          1
9/16/2018   111113    14                          0
9/17/2018   111111    13                          5
9/17/2018   111112    13                          3
9/17/2018   111113    12                          2

The last column is what I am trying to create.

[LivioLanzo]

You can do it like this if you want to add a calculated column, even though a measure would be better

Column =
VAR PrevVal =
    CALCULATE (
        SUM ( Data[Available Count] ),
        ALLEXCEPT (
            Data,
            Data[SKU]
        ),
        Data[Day] = EARLIER ( Data[Day] ) - 1
    )
RETURN
    IF (
        ISBLANK ( PrevVal ),
        BLANK (),
        PrevVal - Data[Available Count]
    )

[Anonymous]

What would be the measure formula?

[LivioLanzo · Accepted solution]

You can do it like this:

Difference With Yesterday =
SUMX (
    SUMMARIZE ( Data, Data[SKU], Data[Day] ),
    VAR ThisDay = Data[Day]
    VAR YestAmount =
        CALCULATE ( SUM ( Data[Available Count] ), Data[Day] = ThisDay - 1 )
    RETURN
        IF (
            ISBLANK ( YestAmount ),
            BLANK (),
            YestAmount - CALCULATE ( SUM ( Data[Available Count] ) )
        )
)

You cannot use the time intelligence PREVIOUSDAY function because you do not have a Date table

[LivioLanzo]

Have a look at the PREVIOUSDAY function

https://msdn.microsoft.com/en-us/query-bi/dax/previousday-function-dax?f=255&MSPPError=-2147217396