# Calculate Last Non Blank SUM of PRevious Day

• ### Question

• Hi All,

I have a case where I need to write a Measure in my Tabular Model.

I have below tables:

Table : RunDates
Column DataType Format
Calendar_Day Date ShortDate

Values
01/01/2099
02/17/2017
02/20/2017
02/21/2017

****Marked my RunDates table as "Date Table"

Table : BalanceAmt
Column DataType Format
Calendar_Day Date ShortDate
ProdID WholeNumber
Balance WholeNumber

Values

Calendar_Day   ProdID     Balance

02/17/2017       1             10
02/17/2017       2             5
02/17/2017       3             7
02/20/2017       2             6
02/21/2017       1             4
02/21/2017       2             5

Now, I should see report like:

Selected Date : 02/17/2017

ProdID TodayBalance PreviousBalance
1             10                   0
2              5                   0
3             7                     0

Selected Date : 02/20/2017

ProdID TodayBalance PreviousBalance
1             10                   10
2             6                     5
3             7                      7

Selected Date : 02/21/2017

ProdID TodayBalance PreviousBalance
1             4                   10
2             5                   6
3             7                   7

in above desired report:
- on 20/Feb/2017 we didn't have any transaction for ProdID 1 so we carried it forward and shown as 10 for both today and previousbalance
- on 20/Feb/2017 we dind't have any transaction for ProdID 3 so we carried it forward and shown as 7 for both todat and previousbalance, the same gets carried over to 21/Feb/2017

I used below formula:

Sum_Balance:=SUM(BalanceAmt[Balance])

TodayBalance:=CALCULATE([Sum_Balance],USERELATIONSHIP(Balance[CCALENDAR_DAY],RunDates[CALENDAR_DAY]))
Previous:=Calculate([Sum_Balance],LASTNONBLANK(PREVIOUSDAY(RunDates[CALENDAR_DAY]),Calculate([Sum_Balance])))

I am getting TodayBalance calculated properly but not the previous balance.
I am not able to understand as why PREVIOUSDAY is no picking up 17/Feb/2017 when selecting date as 20/Feb/2017.

Then I tried below formular but getting Circular Dependency error:

--Created a Calculated Column to check WeekDay Name

WeekName =format([Date],"dddd")

YesterdaySale:=IF (Sales[WeekName]="Monday",Calculate(sum(Sales[SaleAmt]),PREVIOUSDAY(Dates[Dates])),2)

Not getting what I am looking for.

I know that normal PREVIOUSDAY will not work as I have a break in Dates (don't have dates for Saturday & Sunday). So i guess I want to :

- Check if date = Monday

then calculate sum of Friday (if its not Null)

If Friday is NULL then calculate sum of Thursday

- If date !=Monday

then calculate sum of Previous Dya (which is working fine)

Regards
Gurpreet Sethi

Thursday, February 23, 2017 10:54 AM

• Well After going thru couple of options and readings (based on my limited knowleged of DAX) I came up with this solution i.e

1) Added a Key in RunDates table (Primary Key)

Table : RunDates
Column DataType Format
DateID           INT
Calendar_Day Date ShortDate

Values
1 02/17/2017
2 02/20/2017
3 02/21/2017
4 01/01/2099

2) Add the same in BalanceAmt table as well

Table : BalanceAmt
Column DataType Format
DateID   INT
Calendar_Day Date ShortDate
ProdID WholeNumber
Balance WholeNumber

Values
1 02/17/2017 1 10
1 02/17/2017 2 5
1 02/17/2017 3 7
2 02/20/2017 2 6
3 02/21/2017 1 4
3 02/21/2017 2 5

3) Create a Calculated Column to calculate Previous Day in BalanceAmt table

PreviousDay=CALCULATE(Max(BalanceAmt[Calendar_Day]),(FILTER(BalanceAmt,EARLIER(BlanceAmt[DateId])<>BalanceAmt[DateId] && EARLIER(BlanceAmt[Date])>=BalanceAmt[Calendar_Date])))

4) Create a Calculated Column to calculate Previous Days Balance Amount

PreviousBalanceAmt=CALCULATE(Sum(BalanceAmt[BalanceAmt]),(FILTER(Balance,EARLIER(Balance[DateId])<>Balance[DateId] && EARLIER(Balance[PreviousDay])>=Balance[Calendar_Date])))

5) Create a meaure to calculate maximum sale

Support:=max(Sales[PreviousBalanceAmt])

6) Create a meaure to calculate distinct maximum for the selected date.

YesterdaySale:=sumx(Distinct(Sales[PreviousBalanceAmt]),[Support])

[b]Pros:[/b]

1) I got what is desired ... Hurray..

[b]Cons:[/b]

1) Table is more wider (more column it has to spend more time in order to compress on each processing)
2) Duplication of data

I am sure there is another way to do it (more efficient) , waiting for your vaulable feedback to point me to it.

Regards
Gurpreet Sethi

Friday, February 24, 2017 4:00 AM

### All replies

• Well After going thru couple of options and readings (based on my limited knowleged of DAX) I came up with this solution i.e

1) Added a Key in RunDates table (Primary Key)

Table : RunDates
Column DataType Format
DateID           INT
Calendar_Day Date ShortDate

Values
1 02/17/2017
2 02/20/2017
3 02/21/2017
4 01/01/2099

2) Add the same in BalanceAmt table as well

Table : BalanceAmt
Column DataType Format
DateID   INT
Calendar_Day Date ShortDate
ProdID WholeNumber
Balance WholeNumber

Values
1 02/17/2017 1 10
1 02/17/2017 2 5
1 02/17/2017 3 7
2 02/20/2017 2 6
3 02/21/2017 1 4
3 02/21/2017 2 5

3) Create a Calculated Column to calculate Previous Day in BalanceAmt table

PreviousDay=CALCULATE(Max(BalanceAmt[Calendar_Day]),(FILTER(BalanceAmt,EARLIER(BlanceAmt[DateId])<>BalanceAmt[DateId] && EARLIER(BlanceAmt[Date])>=BalanceAmt[Calendar_Date])))

4) Create a Calculated Column to calculate Previous Days Balance Amount

PreviousBalanceAmt=CALCULATE(Sum(BalanceAmt[BalanceAmt]),(FILTER(Balance,EARLIER(Balance[DateId])<>Balance[DateId] && EARLIER(Balance[PreviousDay])>=Balance[Calendar_Date])))

5) Create a meaure to calculate maximum sale

Support:=max(Sales[PreviousBalanceAmt])

6) Create a meaure to calculate distinct maximum for the selected date.

YesterdaySale:=sumx(Distinct(Sales[PreviousBalanceAmt]),[Support])

[b]Pros:[/b]

1) I got what is desired ... Hurray..

[b]Cons:[/b]

1) Table is more wider (more column it has to spend more time in order to compress on each processing)
2) Duplication of data

I am sure there is another way to do it (more efficient) , waiting for your vaulable feedback to point me to it.

Regards
Gurpreet Sethi

Friday, February 24, 2017 4:00 AM
• Hi GURSETHI,

I am glad to know that you have figured out a solution for your issue.