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.
Hello,
I have an annual goals table like that:
ID, Anual Target, year
1,12000,2018
2,24000,2018
3,15000,2018
(...)
How can I generate a new table with monthly goals using the annual goals table in order to make easier to generate graphs like that?
ID,Date, Monthly goal
1,01/01/2018,1000 // result of 12000 / 12
1,02/01/2018,1000
1,03/01/2018,1000
1,04/01/2018,1000
(...)
1,12/01/2018,1000 // last month of 2018
2,01/01/2018,2000 // result of 24000 / 12
2,02/01/2018,2000
2,03/01/2018,2000
2,04/01/2018,2000
(...)
2,12/01/2018,2000 // last month of 2018
(...)
Thanks for your time and sharing knowledge
Solved! Go to Solution.
Hi @Anonymous,
You could create such a calculated table:
Table 1 = ADDCOLUMNS ( CROSSJOIN ( VALUES ( Sheet2[ID] ), FILTER ( CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) ), DAY ( [Date] ) = 1 ) ), "Monthly Goal", LOOKUPVALUE ( Sheet2[Anual Target], Sheet2[ID], [ID] ) / 12 )
Best regards,
Yuliana Gu
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Year", Int64.Type}, {"Target", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..12}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Date", each "1/"&Number.ToText([Custom])&"/"&Number.ToText([Year])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Monthly target", each [Target]/12),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Year", "Target", "Custom"})
in
#"Removed Columns"
Hope this helps.
Hi,
This M code works as well
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MjAwANJGBoYWSrE60UpGII4JmqAxSKUpsmAsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Annual Target" = _t, Year = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Annual Target", Int64.Type}, {"Year", Int64.Type}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From(1)..Number.From(12)}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
#"Added Custom4" = Table.AddColumn(#"Expanded Custom", "Monthly Goal", each [Annual Target]/12),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom4",{{"Year", type text}, {"Custom", type text}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type3", "Custom.1", each "1/"&[Custom]&"/"&[Year]),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Added Custom3", {{"Custom.1", type date}}, "en-IN"),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type with Locale",{"Year", "Custom"}),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.1", type date}}, "en-IN"),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type with Locale1",{"Annual Target"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Removed Columns2",{{"Monthly Goal", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type4",{{"Custom.1", "Date"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Date", "Monthly Goal"})
in
#"Reordered Columns"
Hope this helps.
You are welcome. If my reply helped, please mark it as Answer.
Hi @Anonymous,
You could create such a calculated table:
Table 1 = ADDCOLUMNS ( CROSSJOIN ( VALUES ( Sheet2[ID] ), FILTER ( CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) ), DAY ( [Date] ) = 1 ) ), "Monthly Goal", LOOKUPVALUE ( Sheet2[Anual Target], Sheet2[ID], [ID] ) / 12 )
Best regards,
Yuliana Gu
How do we make this work with 2 years of targets?
ID,Target,Year
1,2400,2019
1,3200,2020
2,2400,2019
2,3200,2020
Hi,
Share your data, describe your business question and show the expected result.
Same as the question above but, now we have 2 years of months cross joined with the group that we want to list based on the yearly target.
Source
ID Year Target
Group 1 2019 2400
Group 2 2020 3200
Group 1 2019 2800
Group 2 2020 3400
New Table with Monthly Targets
ID Month Target
Group 1 1/1/2019 200
Group 1 2/1/2019 200
Group 1 3/1/2019 200
Group 1 4/1/2019 200
Group 1 5/1/2019 200
Group 1 6/1/2019 200
Group 1 7/1/2019 200
Group 1 8/1/2019 200
Group 1 9/1/2019 200
Group 1 10/1/2019 200
Group 1 11/1/2019 200
Group 1 12/1/2019 200
Group 1 1/1/2020 233.3333333
Group 1 2/1/2020 233.3333333
Group 1 3/1/2020 233.3333333
Group 1 4/1/2020 233.3333333
Group 1 5/1/2020 233.3333333
Group 1 6/1/2020 233.3333333
Group 1 7/1/2020 233.3333333
Group 1 8/1/2020 233.3333333
Group 1 9/1/2020 233.3333333
Group 1 10/1/2020 233.3333333
Group 1 11/1/2020 233.3333333
Group 1 12/1/2020 233.3333333
Group 2 1/1/2019 266.6666667
Group 2 2/1/2019 200
Group 2 3/1/2019 200
Group 2 4/1/2019 200
Group 2 5/1/2019 200
Group 2 6/1/2019 200
Group 2 7/1/2019 200
Group 2 8/1/2019 200
Group 2 9/1/2019 200
Group 2 10/1/2019 200
Group 2 11/1/2019 200
Group 2 12/1/2019 200
Group 2 1/1/2020 283.3333333
Group 2 2/1/2020 283.3333333
Group 2 3/1/2020 283.3333333
Group 2 4/1/2020 283.3333333
Group 2 5/1/2020 283.3333333
Group 2 6/1/2020 283.3333333
Group 2 7/1/2020 283.3333333
Group 2 8/1/2020 283.3333333
Group 2 9/1/2020 283.3333333
Group 2 10/1/2020 283.3333333
Group 2 11/1/2020 283.3333333
Group 2 12/1/2020 283.3333333
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Year", Int64.Type}, {"Target", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {1..12}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Date", each "1/"&Number.ToText([Custom])&"/"&Number.ToText([Year])),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Date", type date}}),
#"Added Custom2" = Table.AddColumn(#"Changed Type1", "Monthly target", each [Target]/12),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Year", "Target", "Custom"})
in
#"Removed Columns"
Hope this helps.
Yes! Thanks! I was trying to wrap my head around the DAX, then moved on to the MQuery and that worked nicely! Thank you!
You are welcome. If my reply helped, mark it as Answer.
Beatiful solution, Yuliana Gu.
Thanks for your time.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |