Forum Discussion
Helper IIDAX help with strange distribution
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.
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.
7 Replies
- amitchandak
Super User
- peterso
I reviewed those files earlier. They aren't relevant for this distribution.
- amitchandak
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 ?
- v-janeyg-msft
Community Support
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.
- v-janeyg-msft
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.
