Forum Discussion
Solution SageCalculate data for every week in matrix
Community ChampionHi,
It might be a bit difficult to calculate if you only have cost and budget data for one day. So, I think the easiest way to solve this is to allocate the budget and costs to day level in powerquery.
The additional steps would be something like this:
#"Added Custom1" = Table.AddColumn(#"Appended Query", "DaysInMonth", each Date.DaysInMonth([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "DateList", each List.Numbers(1,[DaysInMonth])),
#"Expanded DateList" = Table.ExpandListColumn(#"Added Custom2", "DateList"),
#"Added Custom3" = Table.AddColumn(#"Expanded DateList", "DailySum", each [Value]/[DaysInMonth]),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Newdate", each #date(Date.Year([pvm]),Date.Month([pvm]),[DateList])as date),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom4",{{"DailySum", type number}, {"Newdate", type date}})
This way you can allocate the data for each day of the year and from there the calculation works like it does with your Sales data. I hope this helps you to solve your issue and if it does consider accepting this as a solution!
Solution SageHi ValtteriN . Thank you for reply. I didn't fully understand the advice about powerquery. Could you please make an example file based on our example that is attached below?
- ValtteriN
Hi,
Due to information security reasons I am not able to dowload your example file. However, I made a more complete example on how to make yearly budget into a daily one by using powerquery.
Example Data:Commented powerquery:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDJU0lEyNAABpVgdkKgRTBTKN0bjm6DxTdH4Zmh8czS+BRrfEo1vaIAuYIgugOTEWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Budget = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Budget", Int64.Type}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Budget"}), //Here I fill the yearly budget to every month
#"Divided Column" = Table.TransformColumns(#"Filled Down", {{"Budget", each _ / 12, type number}}), //Since now it is on a monthly level. I divide the budget with 12
#"Added Custom1" = Table.AddColumn(#"Divided Column", "DaysInMonth", each Date.DaysInMonth([Date])), //Here I add the amount of days for every month
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "DateList", each List.Numbers(1,[DaysInMonth])), //Now I make a list of values based on previous step
#"Expanded DateList" = Table.ExpandListColumn(#"Added Custom2", "DateList"), //Here the list is expanded
#"Added Custom3" = Table.AddColumn(#"Expanded DateList", "DailySum", each [Budget]/[DaysInMonth]), //Now we transform monthly budget to a daily one
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Newdate", each #date(Date.Year([Date]),Date.Month([Date]),[DateList])as date), //Here I add a proper Date column
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom4",{{"DailySum", type number}, {"Newdate", type date}}) // Some data type changes
in
#"Changed Type1"End result:
- DimaMD
HI ValtteriN ,
your knowledge of power query is excellent but,
It is difficult for me to understand how I should implement this, the problem is that I take data from the OLAP system.
In addition to the date, I have budget items
My complete tables contain the following dataData.Year Data.Mounth id organization id. unit Article budget Budget 2021 01.01.2021 123 321 Article 1 120000 2021 01.01.2021 123 321 Article 2 100000
I understand that the Request has to convert my table.- ValtteriN
Hi,
Here is how you can chage that kind of data to daily budget:
Example:Steps:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz1DMyMDJU0lEyNAABIMOzJDXXUClWB1XWCEnWSCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Budget = _t, Item = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Budget", Int64.Type}}),
//Add amount of months
#"MonthlistS" = Table.AddColumn(#"Changed Type", "Monthlist", each List.Numbers(1,12)), //create list
#"months2"= Table.ExpandListColumn(#"MonthlistS", "Monthlist"), //Here I fill the yearly budget to every month
#"Divided Column" = Table.TransformColumns(months2, {{"Budget", each _ / 12, type number}}), //Since now it is on a monthly level. I divide the budget with 12
#"NewDate1"= Table.AddColumn(#"Divided Column", "Newdate1", each #date(Date.Year([Date]),[Monthlist],1)as date), //Create date from month
#"Changed Type2" = Table.TransformColumnTypes(NewDate1,{{"Newdate1", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type2", "DaysInMonth", each Date.DaysInMonth([Newdate1])), //Here I add the amount of days for every month
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "DateList", each List.Numbers(1,[DaysInMonth])), //Now I make a list of values based on previous step
#"Expanded DateList" = Table.ExpandListColumn(#"Added Custom2", "DateList"), //Here the list is expanded
#"Added Custom3" = Table.AddColumn(#"Expanded DateList", "DailySum", each [Budget]/[DaysInMonth]), //Now we transform monthly budget to a daily one
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Newdate", each #date(Date.Year([Date]),Date.Month([Newdate1]),[DateList])as date), //Here I add a proper Date column
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom4",{{"DailySum", type number}, {"Newdate", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date", "Budget", "Monthlist", "Newdate1", "DaysInMonth", "DateList"}) // Some data type changes and clean up
in
#"Removed Columns"End result: