Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate variance

Hi,

 

I have a table ShipDetails with customer names (FINAL_CLIENT) and order dates (ORDERED_DATE) as the one below.

I created a new table with the distinct customer names.

I calculated the number of order for each one of them.

I also calculated the average time between two orders for each customer :

CLIENT_AVERAGE_TIME_BTW_TWO_ORDERS = DIVIDE(DATEDIFF(MINX(RELATEDTABLE(ShipDetails),ShipDetails[ORDERED_DATE]), MAXX(RELATEDTABLE(ShipDetails),ShipDetails[ORDERED_DATE]),DAY),(Clients[CLIENT_ORDER_NUMBER]-1),BLANK())

I would like to calculate the variance of the time between two orders for each customer but I don't know how I could do it. I tried several ideas but none of them worked.

Could you please help me ?

Thank you very much !

 

ShipDetails

FINAL_CLIENTORDERED_DATE
Anna06/06/2020
Anna08/06/2020
Anna09/06/2020
Frank09/06/2020
Anna13/06/2020
Frank05/06/2020
Frank26/06/2020
Anna30/06/2020
Frank20/06/2020

 

Clients

FINAL_CLIENTCLIENT_ORDER_NUMBERCLIENT_AVERAGE_TIME_BTW_TWO_ORDERSVARIANCE
Anna56 
Frank47 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Just so you know, I finally managed to calculate the variance  for each customer thanks to this video :

     

    https://www.youtube.com/watch?time_continue=853&v=jYvr4histgY&feature=emb_title

     

    Here is the DAX code I used :

     

    I added this column in the Shipdetails table:

     

    TIME_SINCE_THE_PREVIOUS_ORDER =
    VAR PreviousOrderDate =
    CALCULATE(
    MAX('ShipDetails'[ORDERED_DATE]),
    FILTER(
    ALLEXCEPT('ShipDetails', 'ShipDetails'[FINAL_CUSTOMER]),
    'ShipDetails'[ORDERED_DATE]<EARLIER('ShipDetails'[ORDERED_DATE])
    )
    )
    RETURN
    DATEDIFF(PreviousOrderDate, 'OrdersShipDetails'[ORDERED_DATE],DAY)

     

    I added this column in the Customers table (I chose to calculate the standard deviation sigma=sqrt(V), instead of the variance V) :

    TIME_BTW_TWO_ORDERS_STANDARD_DEVIATION = SQRT(VARX.P(RELATEDTABLE(ShipDetails),ShipDetails[TIME_SINCE_THE_PREVIOUS_ORDER]))
     
    Thank you very much for your help !

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , this measure will give you the value of last day, change has a need

     

    Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

      Thank you very much for your quick answer.

      I'm sorry, I'm very new to Power BI and the DAX language and I'm not sure that I understood how the measure works.

      What should I put into 'order'[Qty] ? The number of order per customer ?

      What does Table['Date'] stands for ? I tried with ShipDetails[ORDER_DATE] but it doesn't work.

      Finally, what will the measure return ? Which last date ?

      I'm sorry to bother you again. If you need any more information, I'll be happy to provide it.

      Thank you

       

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , As measure

    Last Day Non Continous = CALCULATE(max('ShipDetails'[ORDERED_DATE]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),ShipDetails[ORDERED_DATE])))

     

    Or new column, last order date

    maxx(filter(Table,[FINAL_CLIENT] =earlier([FINAL_CLIENT]) && ORDERED_DATE = earlier([ORDERED_DATE])),[ORDERED_DATE])

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

      When l try the measure an error message is shown saying that it is not possible to determine a unique value for the ORDERED_DATE column in the table ShipDetails.

      As for the calculated column, it gives me the same date as the one in ORDERED_DATE.

      I tried to fix it but I couldn't.

      If you need anything to understand better the problem, I'll be happy to provide it.

      Thank you again for your help.

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    I have a little doubt, are the two dates you refer to the maximum date and minimum date of each customer or two adjacent dates?

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lionel-msft,

      I refer to two adjacent dates, as you showed on the second image (my purpose is to calculate every time between two consecutive orders, so I can determine if a customer is regular or not).

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Just so you know, I finally managed to calculate the variance  for each customer thanks to this video :

         

        https://www.youtube.com/watch?time_continue=853&v=jYvr4histgY&feature=emb_title

         

        Here is the DAX code I used :

         

        I added this column in the Shipdetails table:

         

        TIME_SINCE_THE_PREVIOUS_ORDER =
        VAR PreviousOrderDate =
        CALCULATE(
        MAX('ShipDetails'[ORDERED_DATE]),
        FILTER(
        ALLEXCEPT('ShipDetails', 'ShipDetails'[FINAL_CUSTOMER]),
        'ShipDetails'[ORDERED_DATE]<EARLIER('ShipDetails'[ORDERED_DATE])
        )
        )
        RETURN
        DATEDIFF(PreviousOrderDate, 'OrdersShipDetails'[ORDERED_DATE],DAY)

         

        I added this column in the Customers table (I chose to calculate the standard deviation sigma=sqrt(V), instead of the variance V) :

        TIME_BTW_TWO_ORDERS_STANDARD_DEVIATION = SQRT(VARX.P(RELATEDTABLE(ShipDetails),ShipDetails[TIME_SINCE_THE_PREVIOUS_ORDER]))
         
        Thank you very much for your help !