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.
Hi everyone,
Hoping you can assist with this distribution, i'll do my best to explain.
I'd like to distribute cost by month in such a way where it's cumulative(?) on the graph until the cost reaches it's end date, essentially ignoring the start date. When the end date is reached, the cost drops off.
For example, Jan 2018 would have $16,000 in cost (sum of project costs for all projects) and Mar 2021 would have $15,000 in cost (sum of project costs minus project A, since the end date has passed (1/30/2021).
Below is the table and I have a date table as well.
Project | Cost | Start Date | End Date |
A | $1,000 | 1/1/2018 | 1/30/2021 |
B | $5,000 | 12/1/2020 | 3/2/2022 |
C | $6,000 | 7/27/2020 | 3/19/2021 |
D | $3,000 | 9/15/2021 | 6/20/2022 |
E | $1,000 | 12/19/2019 | 4/30/2021 |
Thank you in advance.
Solved! Go to Solution.
Hi, @peterso
You can do like this. Create a date table, don't create relationships, and create a measure to calculate the desired result.
Like this:
Date Table = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))
Measure = SUMX(FILTER(ALL('Table'),[End Date]>MIN('Date Table'[Date])),[Cost])
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @peterso
You can do like this. Create a date table, don't create relationships, and create a measure to calculate the desired result.
Like this:
Date Table = CALENDAR(MIN('Table'[Start Date]),MAX('Table'[End Date]))
Measure = SUMX(FILTER(ALL('Table'),[End Date]>MIN('Date Table'[Date])),[Cost])
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @peterso
According to your description, I can understand your calculation logic, sum costs and ignore start date. But it’s not clear what visual you want to use to present the results. Can you add more desired results information?
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@peterso , refer the blog or the file attached if those can help
I reviewed those files earlier. They aren't relevant for this distribution.
@peterso , please share in table format. Let me try. I need to get $16,000 for Jan 2018, that should be Jan 2021. As there is only one project is there on Jan 2018 ?
@peterso ,Not getting your number. Check Page 2 and table 3
Hi - I think maybe it would help to entirely ignore the start date.
Essentially this distribution ignores the start date. It assumes all projects are started as as long as the end date is not past today's date.
Once the end date for a project has passed, the project costs drop off.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |