Forum Discussion
Divide value for one date into many dates
Hello,
is there a way to split budget into daily sum for further daily calculations when it is uploaded for the first day of every month. The result should look like this.
I need a measure, since I am connecting to PBI database and I can't create new tables.
Thank you.
elguestca1 , is it monthly or yearly
Check these option
Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
New table way
Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290elguestca1 Create a measure:
DailyBudget =
VAR CurrentMonth = MONTH(MAX('DateTable'[Date]))
VAR MonthlyBudget = MAXX(
FILTER(
'BudgetTable',
MONTH('BudgetTable'[Date]) = CurrentMonth
),
'BudgetTable'[Budget]
)
VAR DaysInMonth = DAY(EOMONTH(MAX('DateTable'[Date]), 0))
RETURN
DIVIDE(MonthlyBudget, DaysInMonth, 0)Add the DateTable as the axis in your visuals.
Use the DailyBudget measure to see the distributed daily budget.💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
3 Replies
elguestca1 , is it monthly or yearly
Check these option
Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
New table way
Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290elguestca1 Create a measure:
DailyBudget =
VAR CurrentMonth = MONTH(MAX('DateTable'[Date]))
VAR MonthlyBudget = MAXX(
FILTER(
'BudgetTable',
MONTH('BudgetTable'[Date]) = CurrentMonth
),
'BudgetTable'[Budget]
)
VAR DaysInMonth = DAY(EOMONTH(MAX('DateTable'[Date]), 0))
RETURN
DIVIDE(MonthlyBudget, DaysInMonth, 0)Add the DateTable as the axis in your visuals.
Use the DailyBudget measure to see the distributed daily budget.💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedInKedar_Pande
Thank you, but if I am using your suggestion correctly, your suggestions seems to return the correct value only for the first day, not for everyday of the month.
