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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

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

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

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:

IdBenefit Start DateBenefit End DateProject Cost
27609/1/20163/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 MonthsSum of Per Month Saving
Sep720
Oct720
Nov720
Dec720
Jan720
Feb720
Mar720
 Total5038

 

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.

  • 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

    • FreedJustine's avatar
      FreedJustine
      Icon for Helper I rankHelper 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!

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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:

       

      Can you please help me with this