Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
j_w
Helper IV
Helper IV

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

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.

1 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.

View solution in original post

3 REPLIES 3

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.

Hi @dkay84_PowerBI

 

Thanks for your response, it works, really amazing 🙂

dearwatson
Responsive Resident
Responsive Resident

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)

 

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.