Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gmasta1129
Helper III
Helper III

Formula to pull in previous day balance

gmasta1129_0-1668636841115.png

 

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 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".  Please note, this should be applied by flex_acct codes as there are many different codes (ex:75104, 75015) on the report (first column)

 

Also, this report runs 5 days a week and therefore does not include weekend dates so it would need to find the previous run date and not previous date.  

 

Thank you in advance for your help! 🙂 

 

1 ACCEPTED SOLUTION

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 ) )

vbinbinyumsft_0-1669344769464.png

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.

 

View solution in original post

8 REPLIES 8
gmasta1129
Helper III
Helper III

@v-binbinyu-msft The formula above is pulling all zeros even when there is a balance in the previous day.  

I think the issue is the return formula. 

RETURN IF ( DAY ( cur_rd ) = 1 && cd <> 1, 0, IF ( cur_cd = "N", 0, _val ) )

 

 

 

It is correct in this scenario since the last negative balance was on 8/2 and there was no balance on 10/31

gmasta1129_0-1669706249102.png

 

But this scenario has a balance as of 10/31 (previous day) and it is pulling 0 when I should be seeing -3.8 million

gmasta1129_1-1669706344519.png

 

gmasta1129
Helper III
Helper III

Hello @v-binbinyu-msft , 

 

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.     

 

gmasta1129_0-1669152303736.png

 

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 ) )

vbinbinyumsft_0-1669344769464.png

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.

 

v-binbinyu-msft
Community Support
Community Support

Hi @gmasta1129 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1669107457992.png

2. create measure with below dax formula

RK =
VAR cur_flexacct =
    SELECTEDVALUE ( 'Table'[flex_acct] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[flex_acct] = cur_flexacct )
RETURN
    RANKX ( tmp, CALCULATE ( MAX ( 'Table'[Run Date] ) ),, ASC, DENSE )
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 )
RETURN
    IF ( cur_cd = "N", 0, _val )

3. add a table visual with fields and measure

vbinbinyumsft_1-1669107607526.png

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.

gmasta1129
Helper III
Helper III

I entered your formula and the first row is pulling in -805,000 instead of -800,000.

 

Please see screenshot below for reference. 

 

gmasta1129_0-1668642147717.png

 

 

 

djurecicK2
Super User
Super User

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". 

gmasta1129
Helper III
Helper III

Hello @djurecicK2,

 

Thank you for the quick response but I would need the previous day balance to pull into the column.  Threfore 10/31/2022 run date balance of 800,000 should pull into the first row and 0 should pull into the second row. 

djurecicK2
Super User
Super User

Hi @gmasta1129 ,

 Here is a way to do that in DAX by creating a new column:

NewBalance = IF('Table'[RunDate]='Table'[ValueDate],'Table'[Balance],0)

Calc Column.PNG

 

Please consider accepting as solution if this has answered the question.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors