Subtracting the Value of One Day from it's Previous Day

[Anonymous]

Hi everyone, how are you?

I'm finding difficulties here to build a measure. I have 2 colums, one with the date and other with the value. I would like to create a new column or measure, to result in the difference between one date and its previous date.

But my problem is: the column date doesn't have equivalent subsequent dates, I don't have Saturdays and Sundays for example, and if I would like to have the difference between Monday and last Friday, I can't find any DAX formula to resolve it.

Here is a picture of my table and what I would like to resolve:

Is there any way that I can index my dates? Or I assume that the previous day is not necessary DAY - 1 ? 

Thanks!

[Mahesh0016]

VAR  Value = sum(Table[Value])
VAR PreviousRow = CALCULATE( Value , OFFSET( -1, ALLSELECTED('Product'[Product]), ORDERBY('Product'[Product], ASC) ) )
RETURN Value - PreviousRow

If this post helps, please consider accept as solution to help other members find it more quickly.
Thank You!

[Anonymous · Accepted solution]

Hi @Anonymous,

It seems like you need to find out hit nearest working days based on current date.

For his scenario, you can consider adding workday as condition to your expression and max function to find the biggest one from current date.

Measure formula:

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR prevDate =
    CALCULATE (
        MAX ( Table[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            WEEKDAY ( [Date], 2 ) <= 5
                && [Date] < currDate
        )
    )
RETURN
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( 'Table' ), Table[Date] = prevDate )
    )

Regards,

Xiaoxin Sheng