Search code examples
excelpowerquerym

Calculating average days without sales in Power Query (M)

I want to calculate the average days without sales from a table. Im doing a test first with 4 rows, 2 clients each of them with 2 sales and 2 different dates (EDITED: There will be more than 2 dates in real data):

Example Data

I tried this steps:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    #"Tipo cambiado" = Table.TransformColumnTypes(Origen,{{"Nombre", type text}, {"Fecha", type datetime}}),
    #"Fecha extraída" = Table.TransformColumns(#"Tipo cambiado",{{"Fecha", DateTime.Date, type date}}),
    #"Filas ordenadas" = Table.Sort(#"Fecha extraída",{{"Nombre", Order.Ascending}, {"Fecha", Order.Ascending}}),
    #"Índice agregado" = Table.AddIndexColumn(#"Filas ordenadas", "Índice", 1, 1),
    #"Columnas reordenadas" = Table.ReorderColumns(#"Índice agregado",{"Índice", "Nombre", "Fecha"}),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Columnas reordenadas",{{"Índice", "ID"}}),
    #"Calculo Dias" = Table.AddColumn(#"Columnas con nombre cambiado", "Diff", each (try Date.From(#"Columnas con nombre cambiado"[Fecha]{[ID]-2})
    otherwise Date.From([Fecha])) - [Fecha])
in
    #"Calculo Dias"

But this doesn´t differentiate between clients and also gives me minus:

enter image description here

Maybe I should compare rows and match the client but I dont know how to do it. If there is better aproach on doing this, feel free to do so. Thank you!

Solution

  • Group by Nombre as follows:

    Group By

    Then you can define your average days between as a custom column

    = Number.From([MaxDate]-[MinDate])/([Count]-1)