Forum Discussion
Resolver IFormula to pull in previous day balance
Hi gmasta1129 ,
Please adjust the measure with below dax formula:
Measure = VAR cur_rk = [RK] VAR cur_flexacct = SELECTEDVALUE ( 'Table'[flex_acct] ) VAR cur_cd = SELECTEDVALUE ( 'Table'[if ValueDaate/Run Date] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[flex_acct] = cur_flexacct && [RK] = cur_rk - 1 ) VAR _val = CALCULATE ( MAX ( 'Table'[usd_balance] ), tmp ) VAR cur_rd = SELECTEDVALUE ( 'Table'[Run Date] ) VAR _pre_date = CALCULATE ( MAX ( 'Table'[Run Date] ), tmp ) VAR cd = DATEDIFF ( cur_rd, _pre_date, DAY ) RETURN IF ( DAY ( cur_rd ) = 1 && cd <> 1, 0, IF ( cur_cd = "N", 0, _val ) )Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver IHello Anonymous ,
Thank you for your help.
This worked exactly as expected except for one scenario. If there is a zero balance for a certain run date in a specific flex acct then the flex acct does not pull onto the report.
Therefore the formula is finding the latest balance from the latest run date and pulling that value in. Can the formula be tweaked where it finds the previous "Run Date" balance and if there is no balance, then pull in 0?
For example, in the screenshot below, flex_acct 71513, the USD balance on run date 11/1/2022 is -8,785.07 but there is no run date for 10/31/2022. The last run date is 10/24/2022 which means the balance as of 10/31/2022 is 0. For our reporting purposes any flex acct with a zero balance does not pull into the report.
Your formula which is under column "USD Balance (Previous Day)" is pulling in the latest run date balance which is 10/24/2022 of -999,519,51. This is incorrect since the previous day from 11/1/2022 is 10/31/2022. because the balance is 0 on 10/31, you will not see a line on the report for it. it does not pull in. I would be expecting to see 0 instead of -991,529.51.
Hi gmasta1129 ,
Please adjust the measure with below dax formula:
Measure =
VAR cur_rk = [RK]
VAR cur_flexacct =
SELECTEDVALUE ( 'Table'[flex_acct] )
VAR cur_cd =
SELECTEDVALUE ( 'Table'[if ValueDaate/Run Date] )
VAR tmp =
FILTER (
ALL ( 'Table' ),
'Table'[flex_acct] = cur_flexacct
&& [RK] = cur_rk - 1
)
VAR _val =
CALCULATE ( MAX ( 'Table'[usd_balance] ), tmp )
VAR cur_rd =
SELECTEDVALUE ( 'Table'[Run Date] )
VAR _pre_date =
CALCULATE ( MAX ( 'Table'[Run Date] ), tmp )
VAR cd =
DATEDIFF ( cur_rd, _pre_date, DAY )
RETURN
IF ( DAY ( cur_rd ) = 1 && cd <> 1, 0, IF ( cur_cd = "N", 0, _val ) )
Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.