Limit interval in iterator to next sale - with dax.do example

[rks]

Hi guys,

I have a measure (see below)

VAR Iterator =
        CALCULATETABLE(
            SUMMARIZE(
                'Movements',
                'Movements'[ItemId],
                'Filter Date'[Day]
            ),
            'Filter Movement Code'[Number] = 2
        )
RETURN
    SUMX(
        Iterator,
        VAR MinTag = CALCULATE( MIN( 'Filter Date'[Day] ) )
        RETURN
            CALCULATE(
                [Sum of Movements],
                'Filter Movement Code'[Number] = 235,
                DATESBETWEEN( 'Filter Date'[Day], MinTag, MinTag + 14 ),
                ALL( 'Filter Date' )
            )
    )

 This measure iterates over items moved on a given day for a certain reason. For those items I want sum the quantity (sum of movements) with another reason 14 days after the initial movement. 

However, if the same item has been moved again with reason 2 within 14 days, I want to decrease the relevant date-interval from the initial movement (no =  2) until the next movement (no = 2). So basically it's either 14 days if there's no subsequent movement or the number of days between first movement and next movement (of no = 2).

I have tried to adapt the problem:

https://dax.do/tMtgqnPYWGBrjT/

What products where sold in Rockhampton and have been sold within the next two weeks in Seaford. However, if the same product was sold twice within 14 days in Rockhampton the interval shouldn't be 14 days but the number of days between the two sales.

Thank you for your help!

Konstantin

[johnt75 · Accepted solution]

Are you sure that there are no typos in either of the names and that they are both the same?

The SUMX gives you a row context on Iterator, so you should be able to access any of the columns in that table, even though it is a variable.

The only other thing I can think to try is to run the CALCULATETABLE statement in DAX Studio and check the table it comes back with

[rks · Accepted solution]

SUMX (
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE ( 'Movements', 'Movements'[Item], 'Filter Date'[Date] ),
            "nextMovement",
                VAR Day = 'Filter Date'[Date]
                RETURN
                    LOOKUPVALUE (
                        'Filter Date'[Date],
                        'Filter Date'[DateKey], CALCULATE ( MIN ( 'Movements'[DateKey] ), 'Filter Date'[Date] > Day )
                    )
        ),
        'Filter MovementCodes'[Number] IN { "1314" }
    ),
    VAR MinDay =
        CALCULATE ( MIN ( 'Filter Date'[Date] ) ) + 1
    VAR MaxDay =
        MIN (
            MinDay + 14,
            IF ( ISBLANK ( [nextMovement] ), MinDay + 14, [nextMovement] )
        )
    RETURN
        CALCULATE (
            [Movement Qty],
            'Filter MovementCodes'[Number] = "77777",
            DATESBETWEEN ( 'Filter Date'[Date], MinDay, MaxDay ),
            ALL ( 'Filter Date' )
        )
)

[johnt75]

Try

DEFINE
    MEASURE 'Sales'[Problem] =
        VAR Iterator =
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE ( 'Sales', Sales[ProductKey], 'Date'[Date] ),
                    "next sale date",
                    CALCULATE(MIN( Sales[Order Date]), Sales[Order Date] > SELECTEDVALUE ('Date'[Date]))
                ),
                Customer[City] = "Rockhampton"
            )
        RETURN
            SUMX (
                Iterator,
                VAR MinTag =
                    CALCULATE ( MIN ( 'Date'[Date] ) )
                VAR MaxTag = MIN( MinTag + 14, [next sale date])
                RETURN
                    CALCULATE (
                        [Sales Amount],
                        Customer[City] = "Seaford",
                        DATESBETWEEN ( 'Date'[Date], MinTag, MaxTag ),
                        ALL ( 'Date' )
                    )
            )

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year Month],
    "Qty", [Sales Amount],
    "Qty2", [Problem]
)

[rks]

Hi John, thank you for the answer.

When running the code in dax.do I receive a valid result. However, my model (hosted on PPU) has got a different opinion. I receive this error message:

The column "next sale date" does not exist or cannot be used in this expression.

It looks like I cannot access the columns from the "iterator"?

[johnt75 · Accepted solution]

Are you sure that there are no typos in either of the names and that they are both the same?

The SUMX gives you a row context on Iterator, so you should be able to access any of the columns in that table, even though it is a variable.

The only other thing I can think to try is to run the CALCULATETABLE statement in DAX Studio and check the table it comes back with