Forum Discussion
Resolver IIPrevious 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
| AccountNumber | Type | Balance | DepositDate | ProcessDate |
| 123 | A1 | 5 | 11/15/2019 | 11/16/2019 |
| 123 | A2 | 56 | 11/15/2019 | 11/16/2019 |
| 123 | A3 | 65 | 10/25/2019 | 10/31/2019 |
| 123 | A4 | 55 | 10/24/2019 | 10/31/2019 |
| 123 | A5 | 45 | 10/30/2019 | 10/30/2019 |
| 345 | A1 | 54 | 11/15/2019 | 11/30/2019 |
| 345 | A2 | 5 | 10/1/2019 | 10/31/2019 |
| 345 | A3 | 45 | 10/3/2019 | 10/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
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
11 Replies
v-frfei-msftCommunity 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 ) )For more details, please check the pbix as attached.
- Nathaniel_C
Community Champion
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
gcoHi 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
- Nathaniel_C
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