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

FreedJustine's avatar
FreedJustine
Helper I
7 years ago

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!