Generate table for each month with accumulative values having the year

[Borja204]

Hi guys!

I'm struggling with one thing in my reports. I have a fact tables with data per each year-month. Now I've been able to show also info of another fact table. This one comes with the granularity of year, and the formula to get the objective per month is as simple as divide per 12.

What I'm trying to achieve with 0 success is to generate a table that taking the original table, generates a new one  multiplying all the records while dividing the values and acumulating them over those months. 

The original table is like this:

CountryId	TypeId	Year	SalesObjective	ClientObjective
ES	2	2020	12	24
UK	3	2020	36	12
US	3	2020	24	72

I need to generate this:

Im totally lost on this. I don't mind to generate it either in power query or in dax, I'm not been able to do it in any of them... but if I had to choose the preference is power query.

Thanks and regards!

[MFelix · Accepted solution]

Hi @Borja204 ,

What I did was to create a new table with the years / months:

Added a new column to this table with the name of the table with the sales objectives:

SalesObjectives

Expanded the new column

Then added two custom columns:

[Custom.SalesObjective]/12*[Month]

[Custom.ClientObjective]/12*[Month]

Deleted the previous two columns and final result below:

Check full code for the second table below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6pEQAwCATAXk4jgPy1MPTfRqIyh1y3EXB1hcCQ8uGMxuiMwZiMxdiMwzAtKgd7ibw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SalesObjectives),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "TypeID", "Year", "SalesObjective", "ClientObjective"}, {"Custom.Country", "Custom.TypeID", "Custom.Year", "Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Salesobjective", each [Custom.SalesObjective]/12*[Month]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ClientObjective", each [Custom.ClientObjective]/12*[Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.Country", Order.Ascending}, {"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
    #"Sorted Rows"

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg1W0lEyAmEDIwMgZQhmmyjF6kQrhXoD2cYIOWMziAKwXDCqHFCLjpI5UC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryId = _t, TypeId = _t, Year = _t, SalesObjective = _t, ClientObjective = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CountryId", type text}, {"TypeId", Int64.Type}, {"Year", Int64.Type}, {"SalesObjective", Int64.Type}, {"ClientObjective", Int64.Type}}),
    #"Added Custom" = Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Custom", each Table.FromRows(List.Accumulate({1..12}, {}, (s,c) => s & {{c, c/12*[SalesObjective], c/12*[ClientObjective]}}), {"Month", "SalesObjective", "ClientObjective"})), {"SalesObjective", "ClientObjective"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Month", "SalesObjective", "ClientObjective"}, {"Month", "SalesObjective", "ClientObjective"})
in
    #"Expanded Custom"

[Borja204]

Amazing also! Thanks!

[Jihwan_Kim]

Hi, @Borja204 

I assume you have a month-table that looks like below.

Please check the below sample pbix file's link and the formula for creating a new table.

NewTable =

VAR newtables =

SUMMARIZECOLUMNS (

Data[CountryId],

Data[TypeId],

Data[Year],

Months[Month],

"@SalesObjective",

DIVIDE (

CALCULATE ( SUM ( Data[SalesObjective] ) ),

CALCULATE ( COUNTROWS ( Months ), ALL ( Months ) )

),

"@ClientObjective",

DIVIDE (

CALCULATE ( SUM ( Data[ClientObjective] ) ),

CALCULATE ( COUNTROWS ( Months ), ALL ( Months ) )

)

)

VAR newtables2 =

ADDCOLUMNS (

SUMMARIZE (

newtables,

Data[CountryId],

Data[TypeId],

Data[Year],

Months[Month]

),

"SalesObjective",

SUMX (

FILTER (

newtables,

Data[CountryId] = EARLIER ( Data[CountryId] )

&& Months[Month] <= EARLIER ( Months[Month] )

),

[@SalesObjective]

),

"ClientObjective",

SUMX (

FILTER (

newtables,

Data[CountryId] = EARLIER ( Data[CountryId] )

&& Months[Month] <= EARLIER ( Months[Month] )

),

[@ClientObjective]

)

)

RETURN

newtables2

https://www.dropbox.com/s/ybxx3cegc0dvqde/borja.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Borja204]

Hi Jihwan,

So impresive solution!! I'm accepting as answer the one that @MFelix posted just because is in power query and I think is cleaner and easier to understand. But your approach was awesome and I've learned a couple of things from your calculated table formula! 

Big thanks!

[MFelix · Accepted solution]

Hi @Borja204 ,

What I did was to create a new table with the years / months:

Added a new column to this table with the name of the table with the sales objectives:

SalesObjectives

Expanded the new column

Then added two custom columns:

[Custom.SalesObjective]/12*[Month]

[Custom.ClientObjective]/12*[Month]

Deleted the previous two columns and final result below:

Check full code for the second table below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6pEQAwCATAXk4jgPy1MPTfRqIyh1y3EXB1hcCQ8uGMxuiMwZiMxdiMwzAtKgd7ibw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SalesObjectives),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "TypeID", "Year", "SalesObjective", "ClientObjective"}, {"Custom.Country", "Custom.TypeID", "Custom.Year", "Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Salesobjective", each [Custom.SalesObjective]/12*[Month]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ClientObjective", each [Custom.ClientObjective]/12*[Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.Country", Order.Ascending}, {"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
    #"Sorted Rows"

[Borja204]

Thank you Miguel,

Simple and clean!

Regards