Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Accrueing amount in months

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.

ProjectRiskAmountstart dateend date
Project ARisk 11000001-04-201901-07-2019
Project ARisk 2400001-06-201901-01-2020
Project ARisk 3600001-08-201901-07-2020
Project BRisk 17000001-12-201831-03-2020
Project BRisk 2500001-01-201901-10-2019
Project BRisk 32000001-03-201901-02-2020

 

And I need a visual similar to this

 AprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
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

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-cherch-msft
Employee
Employee

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks it worked perfectly!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.