Forum Discussion
Solution SageCalculate data for every week in matrix
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
6 Replies
- amitchandak
Super User
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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8 - ValtteriN
Community Champion
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!- 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:
