locked
Calculate Today and Yesterdays Balance RRS feed

  • 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



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



    • Edited by GURSETHI Sunday, February 19, 2017 11:43 PM added more information
    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



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


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

    Thanks for your question.

    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


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

    Tuesday, February 21, 2017 4:06 AM
  • Hi GURSETHI, 

    You may get the desired total BalanceAmt for weekends. However, you can not slice this total BalanceAmt for weekends by ProductName, as there are no BalanceAmt exists for weekends in BalanceAmt table.

    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

    Thursday, February 23, 2017 6:58 AM