Forum Discussion

phaum1967's avatar
phaum1967
Icon for Resolver I rankResolver I
9 years ago
Solved

mean of max difference between dates by client

Hi,

I m looking for a measure which can give me the average age of clients invoices history. For that, I ve a table (named FacturationSommaire) with for each invoice:

 

IdClient -  Issue Date - amount

 

I would like to have the average duration for all clients and for each client

 

I tried to do like that but i doesnt work:

GEOMEANX(FacturationSommaire;datediff(minx(FacturationSommaire;[Issue Date]);maxx(FacturationSommaire;[Issue Date]);day))

Tank you for help

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    phaum1967

     

    I suggest you create a calculated table to summarize the duration calculation into a column group on client level.

     

    Table =
SUMMARIZE (
    FacturationSommaire,
    FacturationSommaire[IdClient],
    "Duration", CALCULATE (
        1
            * ( MAX ( FacturationSommaire[Date] ) - MIN ( FacturationSommaire[Date] ) ),
        ALLEXCEPT ( FacturationSommaire, FacturationSommaire[Client] )
    )
)

    Then use GEOMEANX() function on that "Duration" column.

     

     

    Regards,

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    phaum1967

     

    I suggest you create a calculated table to summarize the duration calculation into a column group on client level.

     

    Table =
SUMMARIZE (
    FacturationSommaire,
    FacturationSommaire[IdClient],
    "Duration", CALCULATE (
        1
            * ( MAX ( FacturationSommaire[Date] ) - MIN ( FacturationSommaire[Date] ) ),
        ALLEXCEPT ( FacturationSommaire, FacturationSommaire[Client] )
    )
)

    Then use GEOMEANX() function on that "Duration" column.

     

     

    Regards,