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

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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Splitting Values between months of two dates

@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

12 REPLIES 12
Highlighted
Super User IV
Super User IV

Re: Splitting Values between months of two dates

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Re: Splitting Values between months of two dates

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.

Highlighted
Microsoft
Microsoft

Re: Splitting Values between months of two dates

@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

Highlighted

Re: Splitting Values between months of two dates

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

Highlighted
Frequent Visitor

Re: Splitting Values between months of two dates

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

 

Highlighted
New Member

Re: Splitting Values between months of two dates

Is there a way to do this in Excel PowerPivot?

Highlighted
New Member

Re: Splitting Values between months of two dates

Is there a way to do this in Excel?

Highlighted
Anonymous
Not applicable

Re: Splitting Values between months of two dates

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

Highlighted
Helper II
Helper II

Re: Splitting Values between months of two dates

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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors