topic Re: Calculating daily increase where date has gap in DAX Commands and Tips

Calculating daily increase where date has gap

Anonymous — Mon, 09 May 2022 18:07:38 GMT

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)

Re: Calculating daily increase where date has gap

tamerj1 — Mon, 09 May 2022 19:19:42 GMT

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

Re: Calculating daily increase where date has gap

Anonymous — Mon, 09 May 2022 20:37:44 GMT

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

Re: Calculating daily increase where date has gap

Anonymous — Thu, 12 May 2022 02:36:25 GMT

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.

Re: Calculating daily increase where date has gap

Anonymous — Mon, 16 May 2022 14:08:42 GMT

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