Forum Discussion

j_w

Helper IV

9 years ago

Solved

How to generate daily level data from monthly level data?

There is a Budget table: Year MonthNo User MonthlyBudget BusinessDaysInMonth 2017 1 David 2200.00 22 2017 2 David ...

Need Help

v-jiascu-msft

9 years ago

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

v-jiascu-msft

Microsoft Employee

9 years ago

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] )
)