Weekly closingBlance by AccountNo with missing WeekEndDate

[og_gremlins]

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 added a column "WeekEndDate" which is the end date of each week.

I tried to get the last BalanceValue for each "AccountNo" at the end of the week. The problem is that sometimes, AccountNo has no record in a week. Thus value in matrix is wrong. Here is an example : 

If my WeekEndDate is 14/01/22, final week balance should be : 95808,99 + (-1646984,4056) + 91288,25 = -1459887,17

But in my matrix Balance of AccountNo 512411 was not took in the measure. Hence It returns :  (-1646984,4056) + 91288,25 = -1555696,16

I currently use this method I found from another forum and tuned it with my dataset :

lastnonblankdate =

LASTNONBLANK ( 'Positions et mouvements bancaires réels'[Accounting Date], CALCULATE ( SUM ( 'Positions et mouvements bancaires réels'[BalanceDaily] ) ) )

lastnonblankvaue by category: =

VAR _lastnonblankdatebycategory =

    ADDCOLUMNS (

        SUMMARIZE ( 'Positions et mouvements bancaires réels', 'Mapping comptes'[Accounting No] ),

        "@lastnonblankdate", [lastnonblankdate]

    )

RETURN

    IF (

        HASONEVALUE ('Date'[Week End  Date]),

        CALCULATE (

            SUM ( 'Positions et mouvements bancaires réels'[BalanceDaily] ),

            TREATAS (

                _lastnonblankdatebycategory,

                'Mapping comptes'[Accounting No],

                'Date'[Date]

            )

        )

    )

Do somoes ever faced this issue and came up with an alternative or solution.

Thank you!

[123abc · Accepted solution]

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]

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]

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