Forum Discussion

AlainB's avatar
AlainB
Frequent Visitor
7 years ago
Solved

Future Account Balance Development with Fix Cost Budget Table

Hi,

 

I am a little bit stuck at moment.

 

I would like to create a Dashboard with show the future development of an Accouts Balance based on a Budget table.

Here the tables I have.

 

dCalendar
Date | Year | Month | MonthNo | Day | DayNo

 

fScheduledMovements

Description | Category | Amount | Periodicity | MonthNo (of Movement | DayNo (of Movement) | Annual Budget

fAccount

Date | Description | TransactionID | Category | Credit | Debit | Balance

 

Based on the Table fScheduledMovements I would like to create a Dashboard with shows the future development of the Balance of the Account.....I wonder if this is possible via DAX?!

If yes...can anyone give me a hint how I can calculate the future Budget Payments based on MonthNo and DayNo for the Expenses? My goal was to only create one table with one entry per Expense so I would not need to create a Table based on every single day and every year.

 

Thank you in advance for any help and ideas.

 

Regards,

 

Alain

 

 

 

  • AlainB's avatar
    AlainB
    7 years ago

    Hi There,

    Found a way myself:

     

    fBudgetForcast = 
GENERATE(dCalendar; 
    CALCULATETABLE(dBudgetFixCosts;
        FILTER(dBudgetFixCosts;
            dCalendar[DayNo] = dBudgetFixCosts[RenewDayNo]);
        FILTER(dBudgetFixCosts;
            OR(
                dBudgetFixCosts[Periodicity] = "Monthly";
                AND(dBudgetFixCosts[Periodicity] = "Yearly";
                    dCalendar[MonthNo] = dBudgetFixCosts[RenewMonthNo])
            )
        )
    )
)

    The Main issue for was to find the correct logic. Maybe there is a nicer way...but for the moment it works.

    If anyone has a better idea how to sovle my problem, thanks in advance.

     

    Regards,

     

    Alain

1 Reply

  • AlainB's avatar
    AlainB
    Frequent Visitor
    7 years ago

    Hi There,

    Found a way myself:

     

    fBudgetForcast = 
GENERATE(dCalendar; 
    CALCULATETABLE(dBudgetFixCosts;
        FILTER(dBudgetFixCosts;
            dCalendar[DayNo] = dBudgetFixCosts[RenewDayNo]);
        FILTER(dBudgetFixCosts;
            OR(
                dBudgetFixCosts[Periodicity] = "Monthly";
                AND(dBudgetFixCosts[Periodicity] = "Yearly";
                    dCalendar[MonthNo] = dBudgetFixCosts[RenewMonthNo])
            )
        )
    )
)

    The Main issue for was to find the correct logic. Maybe there is a nicer way...but for the moment it works.

    If anyone has a better idea how to sovle my problem, thanks in advance.

     

    Regards,

     

    Alain