Accrueing amount in months

[Anonymous]

Hi all

I have a problem, that I just can't see the solution to.

I have a lot of project that have all estimated their finacial risks together with the earliest date and the finishing date. I need the acrrue the risk amount on the months in the period. I can calcuate the monthly amount but can't figure out how to show it per months.

I have a tabel looking like this (just with a lot more entries) and a date tabel with all unique dates.

Project	Risk	Amount	start date	end date
Project A	Risk 1	10000	01-04-2019	01-07-2019
Project A	Risk 2	4000	01-06-2019	01-01-2020
Project A	Risk 3	6000	01-08-2019	01-07-2020
Project B	Risk 1	70000	01-12-2018	31-03-2020
Project B	Risk 2	5000	01-01-2019	01-10-2019
Project B	Risk 3	20000	01-03-2019	01-02-2020

And I need a visual similar to this

	April	May	June	July	August	September	October	November	December
Project A	3.333	3.333	3.905	571	1.117	1.117	1.117	1.117	1.117
Project B	6.749	6.749	6.749	6.749	6.749	6.749	6.749	6.193	6.193

I can see that the solution somehow would be a tabel with the risks splitted up by months, but how can I create that..

Hope someone is able to help.

Thanks

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create a calendar table first.Then use GENERATE function to get the table.

Table 2 =
FILTER (
    GENERATE ( 'Table', Table1 ),
    Table1[start date] <= 'Table'[Date]
        && Table1[end date] >= 'Table'[Date]
)

Regards,

[Anonymous]

Thanks it worked perfectly!