Help with spreading Rev equally across months with different start and end dates.

[Anonymous]

Hello,

I have ben looking through forum posts to try and find this answer and maybe because I am a newbie this hasnt worked out too well for me .

I am trying to transform my input data of this:

Project	Name	Start Date	Due Date	Revenue
Project 1	Task A	12/04/21	09/09/21	USD 1,200
Project 1	Task B	27/01/21	24/04/21	USD 50,000
Project 2	Task C	01/01/21	31/03/21	USD 6,528

Into an out put like this :

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Project 1
Task A				200	200	200	200	200	200
Task B	12500	12500	12500	12500
Project 2
task C	2176	2176	2176

So it will show the Rev split equally across the months the tasks are active , regardless of which day of the month they have started.

To be honest I have not even come close to getting it right yet , but from looking through the previous post it does seem posible , but I must not be understanding of how to do it my self very well.

Any help would be a life save . Thank you in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Create a calendar table as below.

Then create a calculated column on data table.

Column = 'Table'[Revenue]/(DATEDIFF('Table'[Start Date],'Table'[Due Date],MONTH)+1)

Then create a measure as below.

Measure = IF(SELECTEDVALUE('calendar'[monthno])>=MONTH(SELECTEDVALUE('Table'[Start Date]))&&SELECTEDVALUE('calendar'[monthno])<=MONTH(SELECTEDVALUE('Table'[Due Date])),SELECTEDVALUE('Table'[Column]),0)

Best Regards,

Jay

[amitchandak]

@Anonymous , refer to my blog and the file attached for two approaches

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

Hello @amitchandak,

thank you very much for helping me with this .

I have tried the method on your blog and for some reason it is not splitting evenly , all the months are equal appart from the last , which shoots it up higher than the original value.

total value should equal 1200 but it is actually over shooting.

Below is the mesaure equation I used:

Value by day of Month = CALCULATE(SUMX(SUMMARIZE(filter(CROSSJOIN(''Project','Date'),'Date'[Date] >= 'Project'[Start Date] && 'Date'[Date]<= ''Project'[Due Date]),''Project'[id],'Date'[Date],''Project'[Revenue],''Project' [Start Date],''Project' [Due Date]),DIVIDE(''Project'[Revenue] ,DATEDIFF(''Project' [Start Date],''Project'[Due Date],day)+1)))

Any Idea why this is not pulling through right?