Forum Discussion

og_gremlins's avatar
og_gremlins
Frequent Visitor
2 years ago
Solved

Weekly closingBlance by AccountNo with missing WeekEndDate

Hi everyone, I need help in generating a matrix/bar chart with weekly closing balance. I have two datasets :  Position Date Date has been created based on "AccountingDate" of Position table. I...
balance
DAX
Formula
weekly
  • 123abc's avatar
    123abc
    2 years ago

    It looks like you are dealing with a scenario where you want to calculate the closing balance for each account at the end of each week, considering weeks where there might be no transactions for some accounts. Your current DAX formula attempts to find the last non-blank date for each account and then calculates the sum of daily balances for that date.

    To handle cases where an account has no record in a week, you might need to modify your approach. One way to achieve this is to create a table or a measure that contains all possible combinations of AccountNo and WeekEndDate, and then use that as a basis for your calculations.

    Here's an example of how you could modify your DAX formula:

    1. Create a Calendar Table: Make sure you have a calendar table that includes all the dates in your dataset.

    2. Create a CrossJoin Table: Create a new table or a calculated table that represents all possible combinations of AccountNo and WeekEndDate. You can use CROSSJOIN or other techniques to achieve this.

    CrossJoinTable = CROSSJOIN('Mapping comptes', 'Date')

     

    Modify your DAX formula: Use the new CrossJoinTable to calculate the closing balance.

     

    ClosingBalance =
    CALCULATE (
    SUM ( 'Positions et mouvements bancaires réels'[BalanceDaily] ),
    FILTER (
    CrossJoinTable,
    'Positions et mouvements bancaires réels'[Accounting No] = 'Mapping comptes'[Accounting No]
    && 'Date'[Date] <= 'Date'[Week End Date]
    )
    )

     

    This modification ensures that you are considering all combinations of AccountNo and WeekEndDate, even if there are no records for a particular combination.

    Remember to adjust the table and column names according to your actual dataset structure. Additionally, performance considerations should be taken into account, especially if your dataset is large. If performance becomes an issue, you might need to optimize the model or consider using alternate approaches such as creating relationships between tables.

    Please adapt the code according to your data model and relationships. If you have specific details about your data structure, I can provide more tailored guidance.

     

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

     

    In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

og_gremlins's avatar
og_gremlins
Frequent Visitor
2 years ago

Hi 123abc ,

First of all, thank your for your input and thougt ! In the meantime, I found these formulas that get me the closing Balance of  each Week.

FirstOpeningBalance = 
IF('Positions et mouvements bancaires réels'[RowID] = CALCULATE(MIN('Positions et mouvements bancaires réels'[RowID]), ALLEXCEPT('Positions et mouvements bancaires réels', 'Positions et mouvements bancaires réels'[Accounting No], 'Mapping Entity'[Entity/BU], 'Mapping Entity'[Country])), 'Positions et mouvements bancaires réels'[Closing Balance])
ClosingWeeklyBalance = 
VAR _last_visible_date = MAX('Date'[Week End  Date])
VAR _first_visible_date = MIN('Date'[Week End  Date])
VAR _last_noblank_date = CALCULATE(MAX('Positions et mouvements bancaires réels'[Accounting Date]), REMOVEFILTERS())

VAR _result = 
    IF(
        _first_visible_date <= _last_noblank_date,
        CALCULATE(
            SUM('Positions et mouvements bancaires réels'[Sum_Amount ]),
            'Date'[Week End  Date] <= _last_visible_date,
            ALLEXCEPT(
                'Positions et mouvements bancaires réels', 
                'Positions et mouvements bancaires réels'[Accounting No],
                'Mapping Entity'[Entity/BU], 'Mapping comptes'[Accounting No]
            ),
            'Positions et mouvements bancaires réels'[StartsWith5] = "TRUE"
        ) + CALCULATE(
            SUM('Positions et mouvements bancaires réels'[FirstOpeningBalance]),
            ALLEXCEPT(
                'Positions et mouvements bancaires réels', 
                'Positions et mouvements bancaires réels'[Accounting No],
                'Mapping Entity'[Entity/BU], 'Mapping comptes'[Accounting No]
            ),
            'Positions et mouvements bancaires réels'[StartsWith5] = "TRUE"
        )
    )

RETURN _result

 I think I may be more performant than creating a crossJoin table. But now I still struggle to do the same thing to get the OpeningBalance. The ClosingBalance of a week should be the Opening Balance of the following week. If you have ideas about how I can simply do that in Dax I would glady take your ideas.

 

 

Thank you!

og_gremlins's avatar
og_gremlins
Frequent Visitor
2 years ago

NB: I found a trick, soustracting 7 to last visible date ^^. Thanks again