# Calculate Today and Yesterdays Balance

• ### Question

• Hi Friends,

I am seeking your help to find a solution to one of the problem I am struggling with. I have a Tabular Model in which I have 3 tables example below.

I am looking for results like this:

What I am trying to do is:

* Publish Dates from RunDate for end user selection (She/He can select any date from the list)

* Based on Selected Date I am trying to create 2 measures to show today and yesterday's balance

Example:

*on 15th Yesterdays balance was 0 for all the products

*on 16th Yesterday's balance is equal to 15th and if for a given product there is no entry in 16th then it should grab

yesterdays balance (example for 16th product 3 we don't have any value on 16th so its picking up as of 15th)

*on 17th we don't have any records so its picking up same value as of 16th, and displaying same value for both today and   yesterday

I create a measures which is:

SUM_Amount:=SUM(BalanceAmt[BalanceAmt])

To calculate Todays Values:

TodaysBalance:=Calculate([SUM_Amount],CALCULATE(LASTDATE(BALANCEAMT[DATE],DATESBETWEEN(RunDate[Date],BLANK(),MAX(RunDate[Date])),All(RunDate)),ALL(RunDate))

To calculate Previous values:

PreviousBalance:=Calculate([SUM_Amount],CALCULATETABLE(LASTDATE(BalanceAmt[Date],Datesbetween(RunDate[Date],Blank(),lastDate(Rundate[date])),All(Rundate)),all(RunDate))

These are not giving me the desired results ofcourse I havn't wrote it correctly. Seeking advise as what I am doing wrong here.

Regards

Gurpreet Sethi

Sunday, February 19, 2017 11:41 PM

### All replies

• Hi All,

I finally came up with below formulas for Measures:

TodaysBalance:=CALCULATE(SUM_Amount],USERELATIONSHIP(BalanceAmt[Data],RunDate[Date]))

YesterdaysBalance:=CALCULATE([SUM_Amount],LASTNONBLANK(PREVIOUSDAY(RunDate[Date],Calculate([SUM_Amount])))

These are giving me the desired results but not sure if this is the right way or says Optimized way of doing it.

Any comments/suggestions will me more than welcome.

Regards

Gurpreet Sethi

• Edited by Monday, February 20, 2017 8:12 PM Modified text
Monday, February 20, 2017 8:12 PM
• Hi GURSETHI,

I am glad to know that you have got the desired results based on the measures created by you.

If considering performance problem, then you do not have to create measure TodaysBalance, as measure SUM_Amount will do the job.

For YesterdaysBalance, you can also try following DAX expression:

YesterdaysBalance: = CALCULATE([SUM_Amount],PREVIOUSDAY(RunDate[Date]))

One more thing, please kindly mark your reply as an answer. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Tuesday, February 21, 2017 2:34 AM
• Hi Wilson,

I am using LASTNONBLANK and PREVIOUSDAY together as we have data for the weekdays only i.e. Monday till Friday. So once week is over i.e. on Monday, if should see data of Friday.

Howsoever at present it seems LASTNONBLANK & PREVIOUSDAY is not giving me that information so I am kind of back to square one.

Regards

Gurpreet Sethi