YTD Budget per department and budget is only posted in last of every month

[joanwanjiru]

Hi Guys,

i'm struglling a little bit in calculating budget on my dashboard.

I loaded the monthly-year budget from an excel file.

For every budget row there's a date which is always the end-month-date (e.g. budget of august 2017 is loaded with as date 31/08/2017)

• I already calculated the Daily budget,but now i have to calculate the YTD Budget.

How i can calculate the YTD budget per day? so if i select as date 5-may-2017, i would like to have sum of budget from 1 jan 2017 till 30 april 2017 + 5 days in May)

[joanwanjiru · Accepted solution]

I did solve this, and I am so happy. 
So, I had to format the data:
1. Merged budget and calendar using left join on month and year
2. Expanded date
3. loaded the data onto power bi desktop 
4. created a calculated column getting the days in a month.
5. To get daily budget: divide budgets per accountid by day in a month.

Then, finally created a DAX measure:
Note my first week of the year is last week of the previous yesterday.
Dax below is getting YTD budget per accountid per country

Sales Budget YTD = 

VAR MinDateInYumYear = 
CALCULATE(
    MIN('Calendar'[Date]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Yum Year] = SELECTEDVALUE('Calendar'[Yum Year])
    )
)

VAR MaxDate = MAX ( 'Calendar'[Date])
RETURN
CALCULATE(
    SUM('Fact Budgets/Period'[budget]),
    DATESBETWEEN('Calendar'[Date], MinDateInYumYear, MaxDate))