How to generate daily level data from monthly level data?

[j_w]

There is a Budget table:

Year    MonthNo    User        MonthlyBudget    BusinessDaysInMonth

2017   1                   David       2200.00                   22

2017   2                   David       2000.00                   20

... ...

2017   12                 David       2100.00                   21

2017   1                   James      2200.00                   22

2017   2                   James      2000.00                   20

... ...

2017   12                 James      2100.00                   21

How to generate another daily budget table in Power BI as following:

Date               User       DailyBudget    Day

2017-01-01     David     0                        Sunday

2017-01-02     David     100                    Monday

2017-01-03     David     100                    Tuesday

... ...

2017-12-28     David     100                    Thursday

2017-12-29     David     100                    Friday

2017-12-30     David      0                        Saturday

2017-12-31     David      0                        Sunday

2017-01-01     James     0                        Sunday

2017-01-02     James     100                    Monday

2017-01-03     James     100                    Tuesday

... ...

2017-12-28     James    100                    Thursday

2017-12-29     James    100                    Friday

2017-12-30     James    0                        Saturday

2017-12-31     James    0                        Sunday

Thanks.

[v-jiascu-msft · Accepted solution]

Hi @j_w,

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.

Result =
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN ( 'Date', 'Budget' ),
        'Budget'[ MonthNo] = 'Date'[MonthNum]
            && YEAR ( 'Date'[Date] ) = 'Budget'[Year]
    ),
    "Date", [Date],
    "User", [User],
    "Day", FORMAT ( [Date], "dddd" ),
    "DailyBudget", IF ( WEEKDAY ( [Date], 2 ) IN { 6, 7 }, 0, [DailyBudget] )
)

Best Regards!

Dale

[j_w]

Hi @v-jiascu-msft

Thanks for your solution.

Could you have a look at another post?

http://community.powerbi.com/t5/Desktop/How-to-generate-daily-level-data-from-monthly-level-data-Pos...

Regards