cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

 Id Benefit Start Date Benefit End Date Project Cost Per Month Cost 3639 9/1/2016 9/30/2016 \$6,264.00 2760 9/1/2016 3/31/2017 \$5,038.00 2804 9/1/2016 8/31/2017 \$5,776.00 2452 9/1/2016 4/30/2017 \$17,773.00 2456 10/1/2016 7/30/2017 \$21,066.00 2518 11/1/2016 3/31/2017 \$47,012.00 2539 2/1/2017 4/30/2017 \$17,801.00 2630 7/1/2017 8/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:

 Months Values 9/1/2016 34851 10/1/2016 49653 11/1/2016 96665 12/1/2016 96665 1/1/2017 96665 2/1/2017 114466 3/1/2017 114466 4/1/2017 62416 5/1/2017 26842 6/1/2017 26842 7/1/2017 28644 8/1/2017 7578
1 ACCEPTED SOLUTION
Employee

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

13 REPLIES 13
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 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!

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Employee

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

New Member

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.

Ashley

Frequent Visitor

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!

Helper I

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.

Helper II

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

New Member

Is there a way to do this in Excel?

New Member

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 used

The final output:

Helper I

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors