Forum Discussion
quantity after filtered firstdate
HI, maybe simple, but i'm through, A customer has the choice to order (orderwy) by EDI or FAX. Now i want to know how many FAX orders are placed after the first time they ordered by EDI (by ...
Hi Anonymous
Maby someone can come up with less complicated syntax but until then you can try this, also see the attached.
Measure = VAR __EDIOrders = CALCULATETABLE( GROUPBY( Orders, Orders[CustomerID], "@MaxDate", MINX( CURRENTGROUP(), Orders[Date] ) ), ALL( Orders ), VALUES( Orders[CustomerID] ), KEEPFILTERS( Orders[OrderWay] = "EDI" ) ) RETURN SUMX( __EDIOrders, VAR __maxDate = [@MaxDate] RETURN CALCULATE( COUNTROWS( Orders ), KEEPFILTERS( Orders[OrderWay] = "fax" ), KEEPFILTERS( Orders[Date] > __maxDate ) ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Hi Anonymous
Maby someone can come up with less complicated syntax but until then you can try this, also see the attached.
Measure =
VAR __EDIOrders =
CALCULATETABLE(
GROUPBY(
Orders,
Orders[CustomerID],
"@MaxDate", MINX( CURRENTGROUP(), Orders[Date] )
),
ALL( Orders ),
VALUES( Orders[CustomerID] ),
KEEPFILTERS( Orders[OrderWay] = "EDI" )
)
RETURN
SUMX(
__EDIOrders,
VAR __maxDate = [@MaxDate]
RETURN
CALCULATE(
COUNTROWS( Orders ),
KEEPFILTERS( Orders[OrderWay] = "fax" ),
KEEPFILTERS( Orders[Date] > __maxDate )
)
)
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Mariusz
thanks.. The result is what i expected. But what a DAX syntax 😀
I think this works, please test at your side,
ChilliFAX orders after EDI = VAR _1stEdi = CALCULATE(FIRSTDATE(Orders[Date]),Orders[OrderWay] = "EDI") RETURN IF (_1stEdi <> 0, CALCULATE( --replace COUNTROWS with this to get quantity SUM(Orders[Quantity]), COUNTROWS( FILTER(Orders, Orders[OrderWay] <> "EDI" && Orders[Date] >= _1stEdi ) ) ) )Mariusz, I learn a lot from your posts. Wow, I am going to have to study that one.
