Future Account Balance Development with Fix Cost Budget Table

[AlainB]

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 · Accepted solution]

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