Week over Week Difference

[mwc]

Hello - I'm trying to identify materials that had the greatest change in inventory dollars from today compared to the same day last week.  I've tried 20 different variations of DAX formulas, and I don't think I understand them well enough to get my desired result.  I think I'm not using the filter properly???

Table is called 'Inventory History Table' - let's assume that the current date is 8/8/2019 in this example.

Material	Inventory $	Date Stamp
A	10	8/1/2019
B	12	8/1/2019
C	8	8/1/2019
D	20	8/1/2019
E	15	8/1/2019
A	9	8/8/2019
B	15	8/8/2019
C	10	8/8/2019
D	20	8/8/2019
E	25	8/8/2019

Expected Result

Difference week over week
A	-1
B	3
C	2
D	0
E	10

Week over Week Difference =
(CALCULATE(SUM('Inventory History Table'[Inventory $]),LASTDATE('Inventory History Table'[Date Stamp]))) -
(CALCULATE(SUM('Inventory History Table'[Inventory $]),DATEADD('Inventory History Table'[Date Stamp],-7,DAY)))

The error I get with this particular formula is:

'MdxScript(Model) (19,59) Calculation error in measure 'Inventory History Table'[Week over Week Difference]: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

[Icey · Accepted solution]

Hi @mwc ,

I reproduced your question and it didn’t appear any error. You could try it again.

And usually, the reason your error appears is that if it is a measure which is using any date function which expects contiguous date range, bi-directional filter ends up removing some dates and thus it’s no longer a contiguous date range which could be crashing the measure.

So, you can create a new Date Table and manage relationships with your fact tables to solve it.

1. Create a Date Table:

Date Table = CALENDARAUTO()

 2. Manage relationships:

3. Create a measure:

Week over Week Difference 2 = 
(
    CALCULATE (
        SUM ( 'Inventory History Table'[Inventory $] ),
        LASTDATE ( 'Date Table'[Date])
    )
)
    - (
        CALCULATE (
            SUM ( 'Inventory History Table'[Inventory $] ),
            DATEADD ( 'Date Table'[Date], -7, DAY )
        )
)

Best Regards,

Icey

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

[mwc]

Thanks, I think it was the contiguous date issue.  The date table helped.