Forum Discussion

Sabarikumar7579's avatar
Sabarikumar7579
Icon for Helper I rankHelper 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
Icon for Microsoft Employee rankMicrosoft 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

Sabarikumar7579's avatar
Sabarikumar7579
Icon for Helper I rankHelper I
9 years ago

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