cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.