Calculate data for every week in matrix

[DimaMD]

Hello community!

I need help. In our example we have three tables: sales, costs and budget. Sales table has data for every day, cost and budget both have data for 1 date, which means a plan for 1 month, so for that reason I made a mesure that calculate budget and costs for each day depends on quantity of days in month.

2

How can we calculate budget and costs, so we can see results of weeks? As we can see on scrn2 table doesnt calculate this, instead we have empty cells

1

Complete table has data for several years.

Please help me do the right calculation to solve this task

Example file

[ValtteriN]

Hi,
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!

[DimaMD]

Hi @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?

Example file

[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 data

Data.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:

[amitchandak]

@DimaMD , Having week and year in you table should help in that

new columns like

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]

in case you need to compare

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8