locked
Calculate Last Non Blank SUM of PRevious Day RRS feed

  • 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 ++++

    Thursday, February 23, 2017 10:54 AM

Answers

  • 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 ++++


    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

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


    Friday, February 24, 2017 4:00 AM
  • 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 MSDNFSF@microsoft.com



    Friday, February 24, 2017 9:16 AM