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 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 | |
2760 | 9/1/2016 | 3/31/2017 | $5,038.00 | |
2804 | 9/1/2016 | 8/31/2017 | $5,776.00 | |
2452 | 9/1/2016 | 4/30/2017 | $17,773.00 | |
2456 | 10/1/2016 | 7/30/2017 | $21,066.00 | |
2518 | 11/1/2016 | 3/31/2017 | $47,012.00 | |
2539 | 2/1/2017 | 4/30/2017 | $17,801.00 | |
2630 | 7/1/2017 | 8/30/2017 | $1,802.00 |
I am able to do monthly Cost value with a calculate column. My issue to show the "Project Cost" split between the Start and End Date.
Next these numbers need to agregrate and should be able to show rolling month on month using the start date.
Distribution of months value of the total value.
I have tried so many options and not able to get closer. Any help around this would be highly appreicated
This is the output i am looking for:
Months | Values |
9/1/2016 | 34851 |
10/1/2016 | 49653 |
11/1/2016 | 96665 |
12/1/2016 | 96665 |
1/1/2017 | 96665 |
2/1/2017 | 114466 |
3/1/2017 | 114466 |
4/1/2017 | 62416 |
5/1/2017 | 26842 |
6/1/2017 | 26842 |
7/1/2017 | 28644 |
8/1/2017 | 7578 |
Solved! Go to Solution.
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
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |