Obtenga una lista de fechas de rango y horario

Un usuario Pregunto ✅

mzrkwcz

Estoy haciendo un informe de estimaciones de ventas y estoy atascado en el problema de usar la información de la transacción para crear la tabla de facturas futuras.

Tengo una tabla de ofertas con los siguientes campos:

  • period_start = FECHA
  • period_end = FECHA
  • horario = uno de («una vez», «mensual», «trimestral»)

Ahora necesito crear la tabla de facturas, con la columna de fecha:

  • si la programación es «una vez», debería ser solo una fila que contenga period_start
  • si el programa es «mensual», quiero obtener una fila con la fecha de inicio del período y las filas siguientes con fechas incrementadas en 1 mes pero que no excedan la fecha de finalización del período
  • si el programa es «trimestral», quiero obtener un resultado similar al anterior, con un incremento de 3 meses.

No tengo ni idea de cómo empezar … ¿me pueden sugerir algo, por favor?

ImkeF

En respuesta a Greg_Deckler

Esto debería ayudarlo a comenzar:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then {[period_start]} else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"

Crea filas adicionales por mes o trimestre con sus respectivos primeros días.

@Greg_Deckler: ¿Sabes dónde publicar / abordar los errores del foro como las firmas que faltan?

sam241

¿Qué tal esto? (para cualquier otra persona que todavía esté interesada)

= Table.AddColumn (Fuente, «factura_fecha», cada

if schedule = «once» then period_start else

si programa = «mensual», entonces List.Generate (() => [ a = [period_start], b = [period_end] ], cada [a] <= [b], cada [ a = Date.AddMonths( [a], 1), b = [b] ], cada [a] ) demás if schedule = «trimestral» entonces List.Generate (() => [ a = [period_start], b = [period_end] ], cada [a] <= [b], cada [ a = Date.AddQuarters( [a], 1), b = [b] ], cada [a] ) demás nulo )

Y luego expanda las listas, como de costumbre.

En caso de que le sirva de ayuda, este es el documento de referencia para la función List.Generate.

Greg_Deckler

No estoy seguro de que este sea el mejor escenario para Power BI. Parece que está tratando de «inventar» datos con Power BI y probablemente no debería usar Power BI para eso. Es posible que pueda llegar allí con algo como 3 expresiones CALCULATABLES y luego una UNIÓN o dos, pero arriesgadas. También es probable que exista una solución de código M para esto en la que llamaría funciones que devolverían los datos que necesita para sus datos inventados. Pero, probablemente debería usar Excel para generar los datos y luego simplemente importarlos a Power BI.

mzrkwcz

En respuesta a Greg_Deckler

Estaba pensando en usar el código M (para crear una columna con la lista de fechas en la tabla de ofertas) …

No creo que Excel sea una solución aquí; estoy usando los datos leídos directamente desde la API REST de SaaS CRM, Pipedrive. Se actualiza automáticamente y los resultados distribuidos con paneles compartidos son utilizados por otros miembros del equipo.

Greg_Deckler

En respuesta a mzrkwcz

En teoría, podría hacer esto a través del código M con funciones, pero eso me supera un poco, pero @ImkeF probablemente podría mostrarle el camino. Si no puede, entonces ni siquiera es posible.

ImkeF

En respuesta a Greg_Deckler

Esto debería ayudarlo a comenzar:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjA0NzJR0lY0M9QyMYJz8vOVUpVgefktz8vJKMnEoCqgpLE4tKUotA6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [period_start = _t, period_end = _t, schedule = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"period_start", type date}, {"period_end", type date}, {"schedule", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each List.Transform({Number.From([period_start])..Number.From([period_end])}, each Date.From(_))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListOfNewRows", each if [schedule]="once" then {[period_start]} else if [schedule]="monthly" then List.Distinct(List.Transform([ListOfDates], each Date.StartOfMonth(_))) else List.Distinct(List.Transform([ListOfDates], each Date.StartOfQuarter(_)))),
    #"Expanded ListOfNewRows" = Table.ExpandListColumn(#"Added Custom1", "ListOfNewRows")
in
    #"Expanded ListOfNewRows"

Crea filas adicionales por mes o trimestre con sus respectivos primeros días.

@Greg_Deckler: ¿Sabes dónde publicar / abordar los errores del foro como las firmas que faltan?

JohnThomas

En respuesta a ImkeF

¡Increíble! Amo este código. Lo usamos para generar un buen conjunto de datos a partir de un libro de trabajo que tiene fecha de inicio / fecha de finalización para cuando contratamos y ROF (Reduction of Force) Craft empleados. Esto, dado el turno en el que están trabajando (5/10, por ejemplo, serían 5 días a la semana, 10 horas al día) nos permite calcular el costo y las horas pronosticadas con tiempo recto y horas extra por día. Un simple paso de PowerQuery convierte un libro de trabajo súper complicado en una simple línea por empleado. Además, ahora podemos generar gráficos y tablas diarios, semanales, mensuales y trimestrales por empleado, disciplina, área de trabajo, etc.

¡Amo PQ!

mzrkwcz

En respuesta a ImkeF

Vaya, gracias ImkeF, parece que me hiciste empezar, gracias.

Soy nuevo en M, así que necesito experimentar con esto por un tiempo …

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *