How to generate the daily level data from the monthly level data?

[j_w]

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.

[dkay84_PowerBI · Accepted 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.

[j_w]

Hi @dkay84_PowerBI

Thanks for your response, it works, really amazing 🙂

[dearwatson]

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)