Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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' )
)
)
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' )
)
)
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]
)
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"?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |