Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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$
Solved! Go to 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.
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.
@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?
I find the solution. I put ALL( FCT_BALANCES) in filter.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |