Dividing value by month and showing in matrix

[aellison]

I am trying to create a matrix that shows estimated revenue by month. Since this is coming from Dynamic CRM online, I don't have SQL to help get this pivoted the way that I would tend to fall back on, so I need to handle this within PowerBI. I have a dataset that resembles this:

Name	Amount	StartDate	EndDate
John Doe	$ 10,000	1/1/2017	5/1/2017
Jane Smith	$ 6,000	2/1/2017	3/1/2017

I need to divide the Amount by the number of months between StartDate and EndDate, then display them in a matrix fashion to show the amount of estimated revenue by month. In the above example. John's $10,000 is divided by 5 months ($2,000 per month), and Jane's $6,000 is divided by 2 months. These values are added together and displayed in their respective months.

Jan-17	Feb-17	Mar-17	Apr-17	May-17
$ 2,000.00	$ 5,000.00	$ 5,000.00	$ 2,000.00	$ 2,000.00

Any help would be greatly appreciated.

Thanks,

[aellison · Accepted solution]

In order to display this as needed, I created 2 calculated columns and a measure (I know, this could be done in one, but easier to follow this way):

I added a DateDim table with no relationship to to the other table. This is in order to create a cross join effect.

The first calc column is to get the total number of months:

     NumMonths = DATEDIFF(Table1[StartDate], Table1[EndDate],MONTH) +1

The second is to the the Amount by Month:

     AmountByMonth = DIVIDE(Table1[Amount], Table1[NumMonths])

Lastly, the measure get the pivot data:

     EstByMonth =
          CALCULATE(SUM(Table1[AmountByMonth]),
                     FILTER(Table1
                            , ([StartDate] <= LASTDATE(DimDate[Date])
                              && [EndDate] >= FIRSTDATE(DimDate[Date])
                              )
                           )
                    )

[Anonymous]

Hi there,

I have a question regarding the solution of this post. What does the DateDim table contains? and based on the measure you created you meant DateDim[Date] or DimDate[Date] ??

 I want to create a similar matrix but this solution doesnt work for me. 😕

Thanks in advance

[CahabaData]

nicely done. thank you for posting.