Forum Discussion
Payment Forecast & calculated table
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?
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])
)
)
)
)
3 Replies
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.
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?
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])
)
)
)
)
