cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlainB
Frequent Visitor

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

 

 

 

1 ACCEPTED SOLUTION
AlainB
Frequent Visitor

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

View solution in original post

1 REPLY 1
AlainB
Frequent Visitor

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors