Generation of monthly targets table from a table of annual goals

[Anonymous]

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

[v-yulgu-msft · Accepted 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

[Anonymous · Accepted solution]

Thanks @Ashish_Mathur, nice solution.

[Ashish_Mathur]

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.

[Anonymous · Accepted solution]

Thanks @Ashish_Mathur, nice solution.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[v-yulgu-msft · Accepted 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

[Anonymous]

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

[Ashish_Mathur]

Hi,

Share your data, describe your business question and show the expected result.

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

Yes!  Thanks!  I was trying to wrap my head around the DAX, then moved on to the MQuery and that worked nicely!  Thank you!

[Ashish_Mathur]

You are welcome.  If my reply helped, mark it as Answer.

[Anonymous]

Beatiful solution, Yuliana Gu.

Thanks for your time.