cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elietech
Helper II
Helper II

Need help with converting monthly budget to daily budget in a measure

I have data in two tables that looks like this: 

 

Table 1

DateHours
01/01/202145
02/01/202172
03/01/202163

 

Table 2

MonthBudget
JAN1500
FEB1200
MARCH2300

 

I need a measure that I can use in a visualization that will show me a BAR graph of the daily hours, and a line showing the daily budget hours.   

 

Conceptually, the Daily Budget Hours =  (Monthly Budget Hours) / (Num Days In Month)

 

Hopeing to recreate something like this: 

 

I have tried the following, but it's not working and I'm not sure how else to proceed: 

 

 

dailyBudget = 

var budgetValue = CALCULATE(sum('Training Budget Hours'[Budget]),Dates[YearMonth])
var daysInMonth = DAY(EOMONTH(SELECTEDVALUE(Dates[Date]),0))
return divide(budgetValue,daysInMonth,0)

 

 

 

This is what I am hoping to achieve: 

 

elietech_1-1625677134573.png

 

1 ACCEPTED SOLUTION
daxer
Solution Sage
Solution Sage

define
table
    Details = DATATABLE(
        "Date", DATETIME,
        "Hours", INTEGER,
        {
            {"2021-01-01", 145},
            {"2021-02-01", 172},
            {"2021-03-01", 163}
        }
    )
table
    'Monthly Budget' = DATATABLE(
        "Month", STRING,
        "Month Number", INTEGER,
        "Budget", INTEGER,
        {
            {"jan", 1, 1500},
            {"feb", 2, 1200},
            {"mar", 3, 2300}
        }
    )
EVALUATE
    ADDCOLUMNS(
        Details,
        // This will be a calculated column
        // in your Details table
        "Daily Budget",
            var CurrentDate = Details[Date]
            var MonthNumber_ = MONTH( CurrentDate )
            var MonthlyBudget =
                LOOKUPVALUE(
                    'Monthly Budget'[Budget],
                    'Monthly Budget'[Month Number],
                    MonthNumber_,
                    BLANK() -- if no value found, return BLANK
                )
            var NumOfDaysInMonth =
                COUNTROWS(
                    CALENDAR(
                        EOMONTH( CurrentDate, -1 ) + 1,
                        EOMONTH( CurrentDate, 0 )
                    )
                )
            var Result =
                DIVIDE( MonthlyBudget, NumOfDaysInMonth )
            return
                Result
    )
    
// Having such a column in the Details table
// enables you to get the budget for any
// period of time. Just create a measure
// that uses the calculated column "Daily Budget".

View solution in original post

1 REPLY 1
daxer
Solution Sage
Solution Sage

define
table
    Details = DATATABLE(
        "Date", DATETIME,
        "Hours", INTEGER,
        {
            {"2021-01-01", 145},
            {"2021-02-01", 172},
            {"2021-03-01", 163}
        }
    )
table
    'Monthly Budget' = DATATABLE(
        "Month", STRING,
        "Month Number", INTEGER,
        "Budget", INTEGER,
        {
            {"jan", 1, 1500},
            {"feb", 2, 1200},
            {"mar", 3, 2300}
        }
    )
EVALUATE
    ADDCOLUMNS(
        Details,
        // This will be a calculated column
        // in your Details table
        "Daily Budget",
            var CurrentDate = Details[Date]
            var MonthNumber_ = MONTH( CurrentDate )
            var MonthlyBudget =
                LOOKUPVALUE(
                    'Monthly Budget'[Budget],
                    'Monthly Budget'[Month Number],
                    MonthNumber_,
                    BLANK() -- if no value found, return BLANK
                )
            var NumOfDaysInMonth =
                COUNTROWS(
                    CALENDAR(
                        EOMONTH( CurrentDate, -1 ) + 1,
                        EOMONTH( CurrentDate, 0 )
                    )
                )
            var Result =
                DIVIDE( MonthlyBudget, NumOfDaysInMonth )
            return
                Result
    )
    
// Having such a column in the Details table
// enables you to get the budget for any
// period of time. Just create a measure
// that uses the calculated column "Daily Budget".

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors