Calculating daily increase where date has gap

[Anonymous]

I have a table with Date and Sales. I want to calculate daily increase. Just want to know the previous day's sales. However my dates are not in order. There are lot of missing dates. I tried to use PREVIOUSDAY and DATEADD functions. But it is not helping because there is gap in my date. Please help me with the DAX formula.
ResultDailyIncreatse = PREVIOUSDAY(Date[Date])
DATEADD(Date[Date], -1, DAY)

[Anonymous · Accepted solution]

Hi @Anonymous ,

I cannot edit the Sales_Table in the Power Query. So I create a new table instead of Sales_Table. You can try my operations on Sales_Table.

Click Home>>Transform data>>Add column>> Add index column>> From 1. Like the following:

Then create a measure on Sales_Table.

Measure =
VAR _nextvalue =
    CALCULATE (
        MAX ( 'Table'[sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = SELECTEDVALUE ( 'Table'[Index] ) + 1
        )
    )
VAR _noevalue =
    MAX ( 'Table'[sales] )
RETURN
    _nextvalue - _noevalue

Or a column.

Column =
VAR _nextvalue =
    CALCULATE (
        MAX ( 'Table'[sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
    )
VAR _noevalue = 'Table'[sales]
RETURN
    _nextvalue - _noevalue

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @Anonymous very much for your reply. It helped me a lot. I apprciate for your help 😀

[Anonymous]

Hi @tamerj1 

Thank you for willing to help. Yes this is a table visual and I have a date table. I am trying to create a measure. But calculted column will help as well if we can get the accurate result. Please find the attached file.

https://drive.google.com/file/d/1euQWRuYiH7Svm2pR2-UstXP56YeooWGF/view?usp=sharing

[tamerj1]

Hi @Anonymous 

I guess this is a table visual? Shall I assume that you have date table? Are the results summarized by sum? Are trying to create a measure or a calculated column