DAX help with strange distribution

[peterso]

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

@amitchandak 

Thank you in advance.

[v-janeyg-msft · Accepted 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.

[v-janeyg-msft]

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.

[amitchandak]

@peterso , refer the blog or the file attached if those can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[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 ?

[amitchandak]

@peterso ,Not getting your number. Check Page 2 and table 3

[peterso]

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.