cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sabarikumar7579
Helper I
Helper I

Splitting Values between months of two dates

I am really stuck here with no direction to go further

 

My data looks like this:

IdBenefit Start DateBenefit End DateProject CostPer Month Cost
36399/1/20169/30/2016$6,264.00 
27609/1/20163/31/2017$5,038.00 
28049/1/20168/31/2017$5,776.00 
24529/1/20164/30/2017$17,773.00 
245610/1/20167/30/2017$21,066.00 
251811/1/20163/31/2017$47,012.00 
25392/1/20174/30/2017$17,801.00 
26307/1/20178/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:

 

MonthsValues
9/1/201634851
10/1/201649653
11/1/201696665
12/1/201696665
1/1/201796665
2/1/2017114466
3/1/2017114466
4/1/201762416
5/1/201726842
6/1/201726842
7/1/201728644
8/1/20177578
1 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)
Capture.PNG

 

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

 

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

Capture2.PNG

 

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

 

Regards,

Charlie Liao

View solution in original post

13 REPLIES 13
alexagalloz9
Frequent Visitor

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 CodeLeadStart FinishTotal Days
PCCO001Jhon25.01.202008.04.20204
PCCO003Jhon 13.03.202009.06.20202
PCCO001Jose30.09.202030.12.20202

 

Desired result will be something like this:

Project CodeQuarterLeadDays
PCCO01Q1Jhon2
PCCO01Q2Jhon2
PCCO01Q3Jose1
PCCO01Q4Jose1
PCCO03Q1Jhon1
PCCO03Q2Jhon1

 

Hope I can find a solution to this. Thanks!

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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:

IdBenefit Start DateBenefit End DateProject Cost
27609/1/20163/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 MonthsSum of Per Month Saving
Sep720
Oct720
Nov720
Dec720
Jan720
Feb720
Mar720
 Total5038

 

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.

@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)
Capture.PNG

 

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

 

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

Capture2.PNG

 

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

 

Regards,

Charlie Liao

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

 

Capture.JPG

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 CodeLeadStart FinishTotal Days
PCCO001Jhon25.01.202008.04.20204
PCCO003Jhon 13.03.202009.06.20202
PCCO001Jose30.09.202030.12.20202

 

Desired result will be something like this:

Project CodeQuarterLeadDays
PCCO01Q1Jhon2
PCCO01Q2Jhon2
PCCO01Q3Jose1
PCCO01Q4Jose1
PCCO03Q1Jhon1
PCCO03Q2Jhon1

 

Hope I can find a solution to this. Thanks!

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
Not applicable

@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?

Is there a way to do this in Excel?

Is there a way to do this in Excel PowerPivot?

Anonymous
Not applicable

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 usedInsert 1.JPG

 

The final output:

 

Insert 2.JPGCan you please help me with this

 

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors