Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@Omega ,
Creé algunos datos:
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:
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.
@Omega ,
Creé algunos datos:
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:
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.
@Omega , Obtener una nueva columna
Date.AddYears([Fecha], -1)
A continuación, agrupen los datos por esta fecha y los anexen con otros datos.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |