Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

5 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Mariusz 

      thanks.. The result is what i expected. But what a DAX syntax ğŸ˜€

       

      • HotChilli's avatar
        HotChilli
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.