Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
There is a budget table base on year and month:
Year MonthNo User Budget
2017 1 David 3100.00
2017 2 David 2800.00
... ...
2017 12 David 3100.00
2017 1 James 3100.00
2017 2 James 2800.00
... ...
2017 12 James 3100.00
In Power BI, how to generate a new daily budget table from the above monthly table, something like:
Date User Budget
2017-01-01 David 100.00
2017-01-02 David 100.00
... ...
2017-12-30 David 100.00
2017-12-31 David 100.00
2017-01-01 James 100.00
2017-01-02 James 100.00
... ...
2017-12-30 James 100.00
2017-12-31 James 100.00
Thanks.
NOTES: I posted the same question yesterday, but cannot find it anywhere in this forum.
Solved! Go to Solution.
Assuming you can use the Query Editor, use the following M code to get the daily amount per month:
let Source = *YOUR TABLE*, #"Added Custom" = Table.AddColumn(Source, "MonthDays", each Date.DaysInMonth(DateTime.FromText([MonthNo]&"/1/"&[Year]))), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Budget", Int64.Type}, {"MonthDays", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "DailyBudget", each [Budget]/[MonthDays]) in #"Added Custom1"
Please note the month number and year fields need to be text data type.
Once you have this daily amount column, create a separate date table that has every day and a column for month number as well. Something like this:
let Source = #date(2017,1,1), Dates = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1]), type number), #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), type text), #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month",{{"Month", type text}}) in #"Changed Type1"
Next, perform a merge:
let Source = Table.NestedJoin(Table1,{"MonthNo"},dimDate,{"Month"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Column1"}, {"Column1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Year", "MonthNo", "Budget", "MonthDays"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1", "User", "DailyBudget"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Column1", Order.Ascending}, {"User", Order.Ascending}}), #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Column1] <> null)), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"User", Order.Ascending}, {"Column1", Order.Ascending}}) in #"Sorted Rows1"
This should get you what you want.
Please note, if you dates cover multiple years, you will need to create the unique id and do the merge based on the id and not just month number.
Assuming you can use the Query Editor, use the following M code to get the daily amount per month:
let Source = *YOUR TABLE*, #"Added Custom" = Table.AddColumn(Source, "MonthDays", each Date.DaysInMonth(DateTime.FromText([MonthNo]&"/1/"&[Year]))), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Budget", Int64.Type}, {"MonthDays", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "DailyBudget", each [Budget]/[MonthDays]) in #"Added Custom1"
Please note the month number and year fields need to be text data type.
Once you have this daily amount column, create a separate date table that has every day and a column for month number as well. Something like this:
let Source = #date(2017,1,1), Dates = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1]), type number), #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), type text), #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Month",{{"Month", type text}}) in #"Changed Type1"
Next, perform a merge:
let Source = Table.NestedJoin(Table1,{"MonthNo"},dimDate,{"Month"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"Column1"}, {"Column1"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Year", "MonthNo", "Budget", "MonthDays"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1", "User", "DailyBudget"}), #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Column1", Order.Ascending}, {"User", Order.Ascending}}), #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Column1] <> null)), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",{{"User", Order.Ascending}, {"Column1", Order.Ascending}}) in #"Sorted Rows1"
This should get you what you want.
Please note, if you dates cover multiple years, you will need to create the unique id and do the merge based on the id and not just month number.
A few way to handle this, my approach when faced with differing levels of granularity is to create a common calendar table, then create measures to account for he differerences
A good blog post on the concept here:
http://www.daxpatterns.com/handling-different-granularities/
to get the result like below I would look to divde the monthly budget by the number of days in a month
to get the number of days you could count the days in a calendar table e.g. Days=DISTINCTCOUNT(Calendar[datekey])
join to that calendar by a [monthkey] (e.g. CONCAT Year-MonthNo) in your budget table then divide
measures:
Total Budget = SUM(Table1[Budget])
Days=DISTINCTCOUNT(Calendar[datekey])
DailyBudget = DIVIDE([Total Budget],[Days],0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |