Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Proud to be a Super User!
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.
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
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
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)
Hey Glen,
Good job!
Nathaniel
Proud to be a Super User!
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!
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!
Proud to be a Super User!