Forum Discussion

SimonLTM's avatar
SimonLTM
Regular Visitor
9 years ago
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 ...
Need Help
  • SimonLTM's avatar
    SimonLTM
    9 years ago

    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])
    )
    )
    )
    )

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
SimonLTM
Regular Visitor
9 years ago

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's avatar
    SimonLTM
    Regular Visitor
    9 years ago

    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])
    )
    )
    )
    )