cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Año anterior con M Query

Hola

Tengo dos columnas, columna de ventas y columna de fecha. Usando M Query, sé cómo hacerlo usando DAX, quiero calcular las ventas del año anterior.

La razón por la que necesito una consulta M es porque necesito hacer un poco de pivote y despivoting que no puedo usar DAX.

¡Gracias!

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

@Omega ,

Creé algunos datos:

vyangliumsft_0-1637893894703.png

Este es el código de paso de power query:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9JCgAhDETRu7gWseJ8FvH+1+gmBMph+yDh15xOosSAAOddccsbiMIgJIVOyAqNUBQyod5Pm0IldIVEGApCQFTB9hZPLK5a2J5OkLMWticT8vkVtqcS6hkH27P96PfJeMLiXWZztjSb8/9dHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, amount = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"amount", Int64.Type}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Current year", each #date(Date.Year(DateTime.LocalNow()) , Date.Month(DateTime.LocalNow())+1, 1)),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "last year", each #date(Date.Year(DateTime.LocalNow())-1 , Date.Month(DateTime.LocalNow()), 1)),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "IF", each if 

[date] >=[last year] and [date] < [Current year]

then

1

else

0),

    #"Grouped Rows" = Table.Group(#"Added Custom3", {"IF"}, {{"Count", each _, type table [date=nullable date, amount=nullable number, Current year=date, last year=date, IF=number]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Sum(Table.Column([Count],"amount"))),

    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"date", "amount", "Current year", "last year"}, {"date", "amount", "Current year", "last year"})

in

#"Expanded Count"

Resultado:

vyangliumsft_1-1637893894709.png

Saludos

Liu Yang

Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

View solution in original post

2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

@Omega ,

Creé algunos datos:

vyangliumsft_0-1637893894703.png

Este es el código de paso de power query:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9JCgAhDETRu7gWseJ8FvH+1+gmBMph+yDh15xOosSAAOddccsbiMIgJIVOyAqNUBQyod5Pm0IldIVEGApCQFTB9hZPLK5a2J5OkLMWticT8vkVtqcS6hkH27P96PfJeMLiXWZztjSb8/9dHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, amount = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"amount", Int64.Type}}),

    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Current year", each #date(Date.Year(DateTime.LocalNow()) , Date.Month(DateTime.LocalNow())+1, 1)),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "last year", each #date(Date.Year(DateTime.LocalNow())-1 , Date.Month(DateTime.LocalNow()), 1)),

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "IF", each if 

[date] >=[last year] and [date] < [Current year]

then

1

else

0),

    #"Grouped Rows" = Table.Group(#"Added Custom3", {"IF"}, {{"Count", each _, type table [date=nullable date, amount=nullable number, Current year=date, last year=date, IF=number]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Sum(Table.Column([Count],"amount"))),

    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"date", "amount", "Current year", "last year"}, {"date", "amount", "Current year", "last year"})

in

#"Expanded Count"

Resultado:

vyangliumsft_1-1637893894709.png

Saludos

Liu Yang

Si esta publicación ayuda,entonces considere Aceptarla como la solución para ayudar a los otros miembros a encontrarla más rápidamente.

Syndicate_Admin
Administrator
Administrator

@Omega , Obtener una nueva columna

Date.AddYears([Fecha], -1)

A continuación, agrupen los datos por esta fecha y los anexen con otros datos.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!