topic Re: Calculate data for every week in matrix in DAX Commands and Tips

Calculate data for every week in matrix

DimaMD — Wed, 08 Dec 2021 14:36:20 GMT

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.

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

Complete table has data for several years.

Please help me do the right calculation to solve this task

Example file

Re: Calculate data for every week in matrix

amitchandak — Wed, 08 Dec 2021 15:33:25 GMT

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

Re: Calculate data for every week in matrix

ValtteriN — Wed, 08 Dec 2021 17:21:18 GMT

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!

Re: Calculate data for every week in matrix

DimaMD — Wed, 08 Dec 2021 19:24:12 GMT

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

Re: Calculate data for every week in matrix

ValtteriN — Wed, 08 Dec 2021 21:56:52 GMT

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:

Re: Calculate data for every week in matrix

DimaMD — Thu, 09 Dec 2021 09:49:38 GMT

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.

Re: Calculate data for every week in matrix

ValtteriN — Thu, 09 Dec 2021 10:45:55 GMT

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: