How to create new column that get value from previous row (previous date)

[PeraZo]

Hi,

Can someone kindly help me adding new column ("Sales Previous Day ") that gets value from previous row (or previpus day, week, year)?

The table data is below.

I want to get sales number of the previous days, weeks, years for each "Store ID".

ID	Store ID	Date	Sales	Sales Previous Day (or week or month)
1	1	2021/06/18	369
2	2	2021/06/18	800
3	3	2021/06/18	700
4	1	2021/06/19	400	369
5	2	2021/06/19	850	800
6	3	2021/06/19	750	700
7	1	2021/06/20	500	400

I don't want to do this using DAX like sameperiod previous month, dateadd but show in new column as I want to calculate each row.

I tried to use custom columns and Date.Add function but coundn't reach the solution. 

Thank you.

[PeraZo]

Hi,

Thank you!!

It worked.

I customized your code like below for previous week, month etc. and it's working. 

Previous Week Sales = 

CALCULATE(
    SUM(Table2[Sales]),
    LASTDATE(
    FILTER(
        ALL(Table2[Date] ),
        Table2[Date] < EARLIER([Date]) -6
        )
    ), 
    ALLEXCEPT(Table2,Table2[Store ID])
)

Thank you.

Cheers.

[Fowmy · Accepted solution]

@PeraZo 

You can try this as a new column:

Previous Day Sales = 

CALCULATE(
    SUM(Table2[Sales]),
    LASTDATE(
    FILTER(
        ALL(Table2[Date] ),
        Table2[Date] < EARLIER([Date]) 
        )
    ), 
    ALLEXCEPT(Table2,Table2[Store ID])
)