Payment Forecast & calculated table

[SimonLTM]

Hello MS-BI Community,

I am new here and I want to ask for help with following issue.

I have a contract table about periodic monthy or yearly payments.

contractno	date	enddate	formula	amount
1	01.01.2016	01.12.2016	1m	35
2	01.02.2016		1m	453,54
3	01.03.2016		1j	45,23
4	01.04.2016	01.06.2016	1m	456
5	01.05.2016		1j	6454,65
6	01.06.2016		1m	443,86

Now I want to create a payment forecast table  over next years with the data of the contract table. So far I used a calculated date table, which is related to contracttable, to aggregate the amount with a calculated column in date table like this one:

 forecastmonthly=calculate(
                    sumx(relatedtable(contracttable);contracttable[amount]);
                    filter(contracttable;contracttable[formula]="1m")
                    filter(contracttable;contracttable[startdate]<contracttable[enddate])
                    filter(contracttable;day(contracttable[startdate])=day(date[date])
                    filter(contracttable;contracttable[startdate]>earlier(date[date])
)

This calculation works well, but I have only the forecast of the amounts without the relations to the single contracts.

Now I thought that this is maybe a many to many relationship problem, where it is needed to create a bridge table between the date table and the contract table for presenting all forcasting entrys with the whole data.

Has anybody an idea how to create such a bridge table or generally how to solve this issue?

[Greg_Deckler]

I do not see this as a M2M problem, assuming between your date table and contracttable as I do not see your contractno repeated and I would presume your dates in your date table are unique. If I am reading what you want, I would recommend putting contractno into your legend of your visualization to give you the forecast in the context of each contract.

But, I'm not sure what visualization you are using so I can't be sure this is the answer, but I could imagine something like a column visualization with month as the x-axis, forecast as the y-axis and contractno as the legend.

[SimonLTM]

Hello smoupre,

thank you for your answer. Actually the contractno are not repeated and I tried to putting contractno in legend, but it is reflecting in a 1:1  relation only the values from contracttable not the calculated forecast values (n:1 shows not even the blank). So what can I do to create a calculated forecast table where the contractno are also repeated?

 

[SimonLTM · Accepted solution]

I found the solution in making a crossjoin of the date table and a filtered contract table with dax forumlas generate and calculatedtable. It looks like this:

forecasttable = GENERATE('Date';
                      CALCULATETABLE(Tabelle1;
                             FILTER(Tabelle1;
                                  or(AND(DAY(Tabelle1[startdate])=DAY('Date'[Date]);
                                         Tabelle1[formula]="1m");
                                     AND(DAY(Tabelle1[startdate])=DAY('Date'[Date]);
                                     AND(Tabelle1[formula]="1j";MONTH(Tabelle1[startdate])=MONTH('Date'[Date])))));
                             FILTER(Tabelle1;
                                    Tabelle1[startdate]<=EARLIER('Date'[Date]));
                             FILTER(Tabelle1;
                                     or(AND('Date'[Date]<=Tabelle1[enddate];
                                      NOT(ISBLANK(Tabelle1[enddate])));ISBLANK(Tabelle1[enddate])
)
)
)
)