Calculate average balance in period

[ANM_97]

Hello !

I have this problem:

I have balances on working days . I need to calculate the average balance over a period of time. How can I do this without multiplying the balance from the last non-working day in the balance?

During the period, the balance of the weekend must also be taken into account, which is supposed to be the one from the last non-working day.

Expected result:

in period: 2022/04/29 - 2022/04/ 30 -> balance = 200 $
in period 2022/04/29 - 2022/05/02 -> balance = 400$

[ANM_97]

@v-rzhou-msft  Thank you soooooo much! This measure is WOW!   

[v-rzhou-msft · Accepted solution]

Hi @ANM_97 ,

I suggest you to try this code to create a measure.

Balance in Period = 
VAR _RELATE_VALUE =
    ADDCOLUMNS (
        'DATE',
        "Related Value",
            VAR _LAST_WORKING_DATE_ID =
                YEAR ( 'DATE'[LAST_WORKING_DATE] ) * 10000
                    + MONTH ( 'DATE'[LAST_WORKING_DATE] ) * 100
                    + DAY ( 'DATE'[LAST_WORKING_DATE] )
            RETURN
                CALCULATE (
                    SUM ( FACT_BALANCES[Value] ),
                    FILTER ( FACT_BALANCES, FACT_BALANCES[DIM_DATE_ID] = _LAST_WORKING_DATE_ID )
                )
    )
RETURN
    SUMX ( _RELATE_VALUE, [Related Value] )

Result is as below.

Best Regards,
Rico Zhou

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

[ANM_97]

@v-rzhou-msft  I have a little problem. For start of year 2022, if i select January,  how can I change the measure so as to extract the balance of the last working day from December 2021? 

[ANM_97]

I find the solution. I put ALL( FCT_BALANCES) in filter. 

FILTER ( ALL(FCT_BALANCES), FCT_BALANCES[DIM_DATE_ID] = ....

Thank you !