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

---

Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Answer 1

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

---

Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

Answer 2

Hi GURSETHI, 

I am glad to know that you have figured out a solution for your issue.
Please kindly mark the correct solution as an answer, this could be beneficial to other community members reading this thread.

---

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 [e-mail]