Divide project budget by duration of project in days and return the yearly total budget

[kegoosse]

Hi Everyone,

We have a table with Projects, with a project budget, a start date and an end date. We would like to calculate the yearly overall project budget assuming equal distribution of each budget over all days (ranging from the start date to the end date).

We have a table called 'Projects'

Project ID	Budget	Start date	End date
A	€100,000	01/01/2005	01/01/2020
B	€200,000	01/06/2020	10/06/2021
C	€150,000	15/09/2009	15/06/2016

We have a calculated calendar table

Calendar = CALENDAR(MIN('Projects'[Start date]),MAX('Projects'[End date]))

It contains a calculated column for years (Year = YEAR('Calendar'[Date])

We would like to have a column chart with Year on the axis and the total budget per year as a value.

The question therefore is: How do we calculate the measure for this desired value?

Thank you very much for any help

[amitchandak · Accepted solution]

@kegoosse , I have done a similar distribution problem in the attached file after signature. See if that can help

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[kegoosse]

@amitchandak Thx, your file contained the solution.

If other people ever check this topic the DAX-formula for the measure was the following:

Value by day of Month = CALCULATE(SUMX(SUMMARIZE(filter(CROSSJOIN('Projects','Calendar'),'Calendar'[Date] >= 'Projects'[Start date] && 'Calendar'[Date]<= 'Projects'[End date]),'Projects'[ID],'Calendar'[Date],'Projects'[Budget],'Projects'[Start date],'Projects'[End date]),DIVIDE('Projects'[Budget],DATEDIFF('Projects'[Start date],'Projects'[End date],day)+1)))