Splitting Values between months of two dates

[Sabarikumar7579]

I am really stuck here with no direction to go further

My data looks like this:

Id	Benefit Start Date	Benefit End Date	Project Cost	Per Month Cost
3639	9/1/2016	9/30/2016	$6,264.00
2760	9/1/2016	3/31/2017	$5,038.00
2804	9/1/2016	8/31/2017	$5,776.00
2452	9/1/2016	4/30/2017	$17,773.00
2456	10/1/2016	7/30/2017	$21,066.00
2518	11/1/2016	3/31/2017	$47,012.00
2539	2/1/2017	4/30/2017	$17,801.00
2630	7/1/2017	8/30/2017	$1,802.00

I am able to do monthly Cost value with a calculate column. My issue to show the "Project Cost" split between the Start and End Date. 

Next these numbers need to agregrate and  should be able to show rolling month on month using the start date.

Distribution of months value of the total value.

I have tried so many options and not able to get closer. Any help around this would be highly appreicated

This is the output i am looking for:

Months	Values
9/1/2016	34851
10/1/2016	49653
11/1/2016	96665
12/1/2016	96665
1/1/2017	96665
2/1/2017	114466
3/1/2017	114466
4/1/2017	62416
5/1/2017	26842
6/1/2017	26842
7/1/2017	28644
8/1/2017	7578

[alexagalloz9]

I also need to split values between dates. I tried to do the process you explained and the first part worked perfectley. I created a column using DAX and created a calendar table:

Daysperquarter = 'Dedication planned'[Total Days.]/(DATEDIFF('Dedication planned'[Start],'Dedication planned'[Finish],QUARTER)+1)

However, when I tried to crossjoin those two tables,  there is an error:

"The expression refers tu multiple columns. Multiple columns cannot be converted to scalar value"

This is the DAX formula I am using: FILTER(CROSSJOIN('Dedication planned','Calendar'),'Calendar'[Date]>='Dedication planned'[Start]&&'Calendar'[Date]<='Dedication planned'[Finish].[Date]

Basically what I need is to create a new table (or column) where the total days are split evenly per quarter. When split, I need the days to be filter by Name and project code as well. For instance:

Project Code	Lead	Start	Finish	Total Days
PCCO001	Jhon	25.01.2020	08.04.2020	4
PCCO003	Jhon	13.03.2020	09.06.2020	2
PCCO001	Jose	30.09.2020	30.12.2020	2

Desired result will be something like this:

Project Code	Quarter	Lead	Days
PCCO01	Q1	Jhon	2
PCCO01	Q2	Jhon	2
PCCO01	Q3	Jose	1
PCCO01	Q4	Jose	1
PCCO03	Q1	Jhon	1
PCCO03	Q2	Jhon	1

Hope I can find a solution to this. Thanks!

[Greg_Deckler]

I don't understand what "Project Cost" is an how it should be calculated.

[Sabarikumar7579]

Sorry for the confusion, i have corrected the column name. The 4th Column in the first table contains Project Cost between for the period of the 2 Dates. Considering second Row for example:

Id	Benefit Start Date	Benefit End Date	Project Cost
2760	9/1/2016	3/31/2017	$5,038.00

In this case the Project Value is divided by number of months between those 2 dates to get PerMonthCost (5038 / 7 = 720). Then has to be show on a table month on month. Likewise for all the project Cost is split and to show perMonth Total cost for a given month.

Running Months	Sum of Per Month Saving
Sep	720
Oct	720
Nov	720
Dec	720
Jan	720
Feb	720
Mar	720
Total	5038

So ideally for a given month what would be its total cost. Hope, i was able to explain better.  This is want i am trying to acomplish using DAX for Power BI report. here is the excel file for this sample data.

https://1drv.ms/u/s!Asht7QhPGrBBhyuQpjzcCkc9HUtQ

In this excel sheet only Data sheet goes to PowerBI. Transform Tab is for reference.

[v-caliao-msft · Accepted solution]

@Sabarikumar7579,

To achieve it, you need to create a calendar table and crossjoin your original table and this calendar table.

In your original table, create a column use the DAX below.
CostPerMonth = Table1[Cost]/(DATEDIFF(Table1[StartdDate],Table1[EndDate],MONTH)+1)

Create a calendar table
Calendar = FILTER(CALENDAR("2016-01-01","2017-12-31"),DAY([Date])=1)

Crossjoin those two tables.
Table = FILTER(CROSSJOIN(Table1,'Calendar'),'Calendar'[Date]>=Table1[StartdDate]&&'Calendar'[Date]<=Table1[EndDate].[Date])

And then you can show your expected result by using a martix visual.

Regards,

Charlie Liao

[Anonymous]

Hi Charlie,

Thanks for the solution. Unfortunately, it did not quite work right for me.

If we take line OPP-15167 for example. The total sales budget is £50,853 and should take 6 weeks. I need this budget split evenly over a 6 week period. So, £8,475.50 per week for 6 weeks, instead of £8,475.50 over 2 months.

I am sure it is an easy fix but I cannot work it out, so any help would be greatly appreciated.

Thanks in advance.

Ashley

[alexagalloz9]

I also nnet to split values between dates. I tried to do the process you explained and the first part worked perfectley. I created a column using DAX and created a calendar table:

Daysperquarter = 'Dedication planned'[Total Days.]/(DATEDIFF('Dedication planned'[Start],'Dedication planned'[Finish],QUARTER)+1)

However, when I tried to crossjoin those two tables,  there is an error:

"The expression refers tu multiple columns. Multiple columns cannot be converted to scalar value"

This is the DAX formula I am using: FILTER(CROSSJOIN('Dedication planned','Calendar'),'Calendar'[Date]>='Dedication planned'[Start]&&'Calendar'[Date]<='Dedication planned'[Finish].[Date]

Basically what I need is to create a new table (or column) where the total days are split evenly per quarter. When split, I need the days to be filter by Name and project code as well. For instance:

Project Code	Lead	Start	Finish	Total Days
PCCO001	Jhon	25.01.2020	08.04.2020	4
PCCO003	Jhon	13.03.2020	09.06.2020	2
PCCO001	Jose	30.09.2020	30.12.2020	2

Desired result will be something like this:

Project Code	Quarter	Lead	Days
PCCO01	Q1	Jhon	2
PCCO01	Q2	Jhon	2
PCCO01	Q3	Jose	1
PCCO01	Q4	Jose	1
PCCO03	Q1	Jhon	1
PCCO03	Q2	Jhon	1

Hope I can find a solution to this. Thanks!

[FreedJustine]

Hi, the formula was very useful, but what if you have an overlapping date range.

e.g start date is Jan 15, 2019

end date is feb 10, 2019

how would you group them by month. Thanks!

[Anonymous]

@v-caliao-msft  you are a genius. Thanks, mate this is really useful. 

[powerBIpeon]

@v-caliao-msft - very useful, however I found out that i would get a infinity error when the start and end dates were the same month and year (ex. Start Date = 7/01/2019; End Date: 7/31/2019). Is there a workaround for this?

[tconstantini]

Is there a way to do this in Excel?

[tconstantini]

Is there a way to do this in Excel PowerPivot?

[Anonymous]

I have a similar issue and used the solution to create a table as mentioned.
in my case the total of original value and the crossjoin table is not adding up. Formula used

The final output:

Can you please help me with this

[Sabarikumar7579]

Awsome, Charlie !!!! I got the required output. Thank You