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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

v-caliao-msft  you are a genius. Thanks, mate this is really useful. 

powerBIpeon's avatar
powerBIpeon
Helper II
7 years ago

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?