Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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])
)
)
)
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])
)
)
)
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.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |