Forum Discussion

gmasta1129's avatar
gmasta1129
Icon for Resolver I rankResolver I
3 years ago
Solved

Formula to pull in previous day balance

  Hello,   I am looking to pull the previous run date (second column) USD balance (7th column) if the "if value date/Run date" column is equal to "Y".   For example, the new column should c...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

djurecic's avatar
djurecic
Icon for Super User rankSuper User
3 years ago

Hi gmasta1129 , 

I don't think I understand your requirements. Here is what I was trying to show:

 

For example, the new column should contain a value of -800,000 (previous day USD balance) in the first row and in the second row a value of 0 since the if statement is equal to "N".