Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |