Newbie Help requested for PreviousDay function

[Anonymous]

Hi!

This is my first post... so thanks in advanced to anyone who can help me solve it! I have searched to dee a solution but couldn't easily find it so apologies of this has been solved before.

I have a table that has daily closoing bank balances in it. I am trying to create a DAX measure in Power BI that will return the previous day's balance so I can put as an opening balance comparison.

I have run the following DAX query:

Closing Balance (Previous Day) = CALCULATE(SUM('Bank Balances'[Closing Balance]), PREVIOUSDAY('Dates Table'[Date]))

However it always retuns a blank value. Here is my table structure:

When I run the same query on my transaction table it works correctly summing up the previous days transactions:

Transactions (Previous Day) = CALCULATE(SUM('Transaction Data'[Transaction Amount (Real)]), PREVIOUSDAY('Dates Table'[Date]))

I assume it doesn't work for the balance because I can't use CALCULATE as there is not multiple transactions just a balance but how do I pull in the closing balance value?

Thanks in advance if anyone can help me!

[Anonymous]

Thanks so much... that worked

I now realise my error. Previously I was referencing my dates table but I had prefilled this with dates for the full year so looks like it was pciking up a future date.

Once I used your suggestion and referenced the dates in the balance table it worked perfectly!

[tamerj1]

Hi @Anonymous 

What is the granularity of the date column in the Balances table?

[ValtteriN · Accepted solution]

Hi,

It is bit difficult to approach this without examples of data. One explanation that came to my mind is that your Bank Balances table's date data is not in the correct format. However, disregarding that here is one approach to this issue:

Example data:

measure:

LastdayBalance =

var _Ldate = MAX('Calendar'[Date]) return

CALCULATE(SUM('Balance Example'[Balance]),ALL('Balance Example'[Date]),'Balance Example'[Date]=_Ldate-1)

End result:

PREVIOUSDAY should also work if you include ALL. 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/