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

alexagalloz9's avatar
alexagalloz9
Frequent Visitor
6 years ago

I also need to split values between dates. I tried to do the process you explained and the first part worked perfectley. I created a column using DAX and created a calendar table:

Daysperquarter = 'Dedication planned'[Total Days.]/(DATEDIFF('Dedication planned'[Start],'Dedication planned'[Finish],QUARTER)+1)
 
However, when I tried to crossjoin those two tables,  there is an error:
 
 

"The expression refers tu multiple columns. Multiple columns cannot be converted to scalar value"

This is the DAX formula I am using: FILTER(CROSSJOIN('Dedication planned','Calendar'),'Calendar'[Date]>='Dedication planned'[Start]&&'Calendar'[Date]<='Dedication planned'[Finish].[Date]

 

Basically what I need is to create a new table (or column) where the total days are split evenly per quarter. When split, I need the days to be filter by Name and project code as well. For instance:

 

Project CodeLeadStart FinishTotal Days
PCCO001Jhon25.01.202008.04.20204
PCCO003Jhon 13.03.202009.06.20202
PCCO001Jose30.09.202030.12.20202

 

Desired result will be something like this:

Project CodeQuarterLeadDays
PCCO01Q1Jhon2
PCCO01Q2Jhon2
PCCO01Q3Jose1
PCCO01Q4Jose1
PCCO03Q1Jhon1
PCCO03Q2Jhon1

 

Hope I can find a solution to this. Thanks!