Forum Discussion

mwc's avatar
mwc
Frequent Visitor
6 years ago
Solved

Week over Week Difference

 

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.

MaterialInventory $Date Stamp
A108/1/2019
B128/1/2019
C88/1/2019
D208/1/2019
E158/1/2019
A98/8/2019
B158/8/2019
C108/8/2019
D208/8/2019
E258/8/2019

 

Expected Result

Difference week over week
A-1
B3
C2
D0
E10

 

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.

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

2 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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's avatar
      mwc
      Frequent Visitor
      6 years ago

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