topic Re: Limit interval in iterator to next sale - with dax.do example in DAX Commands and Tips

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

rks — Thu, 14 Jul 2022 14:37:39 GMT

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

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

johnt75 — Thu, 14 Jul 2022 15:58:34 GMT

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] )

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

rks — Fri, 15 Jul 2022 06:14:37 GMT

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"?

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

johnt75 — Fri, 15 Jul 2022 08:48:37 GMT

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

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

rks — Fri, 15 Jul 2022 14:04:04 GMT

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' ) ) )