Forum Discussion

Sabarikumar7579's avatar
Sabarikumar7579
Helper I
9 years ago
Solved

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 ...
Need Help
Show and Tell
Tips and Tricks
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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)

     

    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

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

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)

 

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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