cancel
Showing results for
Did you mean:
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

 Date Hours 01/01/2021 45 02/01/2021 72 03/01/2021 63

Table 2

 Month Budget JAN 1500 FEB 1200 MARCH 2300

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:

1 ACCEPTED SOLUTION
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
Details,
// This will be a calculated column
"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".``````
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
Details,
// This will be a calculated column
"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".``````

Announcements

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.

Microsoft named a Leader in The Forrester Wave

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

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