quantity after filtered firstdate

[Anonymous]

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 customer).

FAX orders after EDI implementation = 

CALCULATE(
    SUM(Orders[Quantity]),
    Orders[OrderWay] <> "EDI",
    FILTER(
        Orders,
        Orders[Date] >= CALCULATE(FIRSTDATE(Orders[Date]),Orders[OrderWay] = "EDI")
        )
    )

example pbix file 

hope someone can help me

with kind regards

[Mariusz · Accepted solution]

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

[Anonymous]

@Mariusz 

thanks.. The result is what i expected. But what a DAX syntax 😀

[HotChilli]

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.

[Anonymous]

Hi @HotChilli 

this one i tried first also, but then total in a matrix isn't correct

[HotChilli]

Totals bring their own challenges of course.

You could try:

MeasTotals = SUMX(VALUES(Klanten), [ChilliFAX orders after EDI])