cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Dividing value by month and showing in matrix

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:

 

NameAmountStartDateEndDate
John Doe$ 10,0001/1/20175/1/2017
Jane Smith$ 6,0002/1/20173/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-17Feb-17Mar-17Apr-17May-17
$ 2,000.00$ 5,000.00$ 5,000.00$ 2,000.00$ 2,000.00

 

Any help would be greatly appreciated.

Thanks,

1 ACCEPTED SOLUTION
Helper I
Helper I

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])
                              )
                           )
                    )

 

View solution in original post

3 REPLIES 3
Helper I
Helper I

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])
                              )
                           )
                    )

 

View solution in original post

Anonymous
Not applicable

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

 

nicely done. thank you for posting.

www.CahabaData.com

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors