Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.