How to generate daily level data from monthly level data?

Question

There is a Budget table:Year &nbsp; &nbsp;MonthNo &nbsp; &nbsp;User &nbsp; &nbsp; &nbsp; &nbsp;MonthlyBudget &nbsp; &nbsp;BusinessDaysInMonth2017 &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; David &nbsp; &nbsp; &nbsp; 2200.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 222017 &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; David &nbsp; &nbsp; &nbsp; 2000.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20... ...2017 &nbsp; 12 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; David &nbsp; &nbsp; &nbsp; 2100.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 212017 &nbsp; 1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; James &nbsp; &nbsp; &nbsp;2200.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 222017 &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; James &nbsp; &nbsp; &nbsp;2000.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 20... ...2017 &nbsp; 12 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; James &nbsp; &nbsp; &nbsp;2100.00 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21&nbsp;How to generate another daily budget table in Power BI as following:Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; User &nbsp; &nbsp; &nbsp; DailyBudget &nbsp; &nbsp;Day2017-01-01 &nbsp; &nbsp; David &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sunday2017-01-02 &nbsp; &nbsp; David &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Monday2017-01-03 &nbsp; &nbsp; David &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Tuesday... ...2017-12-28 &nbsp; &nbsp; David &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Thursday2017-12-29 &nbsp; &nbsp; David &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Friday2017-12-30 &nbsp; &nbsp; David &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Saturday2017-12-31 &nbsp; &nbsp; David &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sunday2017-01-01 &nbsp; &nbsp; James &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sunday2017-01-02 &nbsp; &nbsp; James &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Monday2017-01-03 &nbsp; &nbsp; James &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Tuesday... ...2017-12-28 &nbsp; &nbsp; James &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Thursday2017-12-29 &nbsp; &nbsp; James &nbsp; &nbsp;100 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Friday2017-12-30 &nbsp; &nbsp; James &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Saturday2017-12-31 &nbsp; &nbsp; James &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Sunday&nbsp;Thanks.

v-jiascu-msft · Accepted Answer

Hi&nbsp;j_w,&nbsp;First, we need a date table.Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    "MonthNum", MONTH ( [Date] )
)Second, we add a column "DailyBudget" in the table "Budget".DailyBudget =
'Budget'[ MonthlyBudget] / 'Budget'[ BusinessDaysInMonth]Third, we would get the result with this formula.&nbsp;Result =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN ( 'Date', 'Budget' ),
        'Budget'[ MonthNo] = 'Date'[MonthNum]
            &amp;&amp; YEAR ( 'Date'[Date] ) = 'Budget'[Year]
    ),
    "Date", [Date],
    "User", [User],
    "Day", FORMAT ( [Date], "dddd" ),
    "DailyBudget", IF ( WEEKDAY ( [Date], 2 ) IN { 6, 7 }, 0, [DailyBudget] )
)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Best Regards!Dale

Forum Discussion

How to generate daily level data from monthly level data?

2 Replies

Related content

Monthly sales budget data allocation to daily grain

Monthly Budget to Daily

Monthly into Daily Resolution

Monthly Automatic Change

Auto-scale Daily/Monthly axis with field parameter – chart still shows wrong grain