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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gco
Resolver II
Resolver II

Previous 15 day balance

Hi there,

 

I am trying to figure out how to get the beginning (15 days ago) and end balance for a specific table.

 

If i select DepositDate of 11/15/2019 from the slicer.  I would like to get the following:

 

EndBalance (123) = 61  -- (5+56) based on DepositDate

BeginningBalance(123) = 120   -- (55+65) based on ProcessDate = DepositDate - 15 days

BalanceType(A1 for 123) = 5 -- based on DepositDate

 

Same calculation for Account Number 345 

 

AccountNumberTypeBalanceDepositDateProcessDate
123A1511/15/201911/16/2019
123A25611/15/201911/16/2019
123A36510/25/201910/31/2019
123A45510/24/201910/31/2019
123A54510/30/201910/30/2019
345A15411/15/201911/30/2019
345A2510/1/201910/31/2019
345A34510/3/201910/30/2019

 

I tried the following measure for BeginningBalance but i am getting blank.

 

= CALCULATE (SUM(table[Balance]), FILTER(table,table[processdate] = SELECTEDVALUE(table[DepositDate])-15)

 

Your help is very much appreciated!

Thank you

Glen

1 ACCEPTED SOLUTION

Hi Glen,
Here you go...PBIX 
In the message it is right above my name.  The formatting in those messages leave something to be desired.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
v-frfei-msft
Community Support
Community Support

Hi @gco ,

 

To create the measures as below.

BalanceType(A1 for 123) = 
VAR seld =
    SELECTEDVALUE ( 'Table 2'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER (
            'Table',
            'Table'[AccountNumber] = 123
                && 'Table'[DepositDate] = seld
                && 'Table'[Type] = "A1"
        )
    )
BeginningBalance(123) = 
VAR selpre15 =
    SELECTEDVALUE ( 'Table 2'[Date] ) - 15
RETURN
    CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER (
            'Table',
            'Table'[AccountNumber] = 123
                && 'Table'[ProcessDate] = selpre15
        )
    )
EndBalance (123) = 
VAR seld =
    SELECTEDVALUE ( 'Table 2'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Balance] ),
        FILTER ( 'Table', 'Table'[AccountNumber] = 123 && 'Table'[DepositDate] = seld )
    )

Capture.PNG

For more details, please check the pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Nathaniel_C
Super User
Super User

Hi @gco ,

Does this look like what you are after?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Beg Bal.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C ,

 

Can you please tell me where i could download the pbix file?  I seem to be missing the download link.

Thank you

Glen

Hi Glen,
Here you go...PBIX 
In the message it is right above my name.  The formatting in those messages leave something to be desired.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Nathaniel_C 

 

Your answer was spot on!  Thank you very much.  I just had to change some of the measures as it did not seem to give me the exact calculation.

 

here are the changes i made to your original measures:

End Account Balance = (original measure only gives me the max value of the previous process date for some reason)
VAR _procDate = SELECTEDVALUE(table[DepositDate])
RETURN
CALCULATE (SUM (table[balance]), ALLEXCEPT(table, table[AccountNumber], table[processdate] = _procDate)

BalanceByType = (had to add _Type variable so that i can add more types in the slicer)
VAR _depDate = SELECTEDVALUE(AccountInfo[OriginalDepositDate])
VAR _Type = SELECTEDVALUE(AccountTransaction[ShareType])
VAR _calc = CALCULATE(MAX(AccountInfo[ShareBalance]), AccountInfo[OriginalDepositDate] = _depDate, AccountTransaction[ShareType] = _Type, ALLEXCEPT(AccountInfo, AccountInfo[AccountNumber]))
RETURN _calc

 

Hey Glen,

Good job!

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Nathaniel_C ,

 

Do you think i could add a loop on the totals calculation?  Now that it works, our Marketing department would like to see if i can show a card with the totals for all the deposit dates based on BalanceByType.

 

Here is my calculation that displays the total by type.

 

IF (HASONEVALUE(Table[AccountNumber]),

MAXX (Table, [BalanceByType]),

SUMX (

        SUMMARIZE (

                 Table,

                Table[AccountNumber],

                "max",[BalanceByType]

          ),

         [max]

))

Hi @Nathaniel_C ,

 

Please disregard, i actually put the Total in a table with the deposit date and it showed the total aggregate :).  So i was just overthinking it.

 

Thanks again

 

Glen

Not that I ever do that!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you so much @Nathaniel_C !!!

 

@v-frfei-msft., i am also looking at the other PBIX file.  thank you

@gco , you are welcome!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors