Forum Discussion
Solved
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 ...
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])
)
)
)
)
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])
)
)
)
)