Forum Discussion
Splitting Values between months of two dates
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
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.
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
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!
Awsome, Charlie !!!! I got the required output. Thank You
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 usedThe final output:
Can you please help me with this
Is there a way to do this in Excel PowerPivot?
Is there a way to do this in Excel?
v-caliao-msft you are a genius. Thanks, mate this is really useful.
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?
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!
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
