Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Daily Balance Calculation

Hi, I am trying to get the daily closing balance of my data. I currently have dates given by 'Date'[Date] showing the working days (weekends are blank) of debits and credits being logged. At th...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    Please update the formula of measure as below and check if it can return the expected result...

    DailyClosingBalance =
VAR CurrentDate =
    SELECTEDVALUE ( 'GL_Account'[glt_trdate] )
VAR CurrentPeriod =
    SELECTEDVALUE ( 'GL_Period'[id_glperiod] )
VAR PreviousPeriod =
    MAXX (
        FILTER (
            ALLSELECTED ( 'GL_Period'[id_glperiod] ),
            'GL_Period'[id_glperiod] < CurrentPeriod
        ),
        'GL_Period'[id_glperiod]
    )
VAR PreviousClosingBalance =
    CALCULATE (
        MAX ( 'GL_Account'[ClosingBalance] ),
        FILTER (
            ALLSELECTED ( 'GL_Period' ),
            'GL_Period'[id_glperiod] = PreviousPeriod
        ),
        ALL ( 'Date' )
    )
VAR PreviousDailyNetBalance =
    SUMX (
        FILTER ( ALLSELECTED ( 'GL_Account' ), 'GL_Account'[glt_trdate] <= CurrentDate ),
        'GL_Account'[Daily Net Balance]
    )
RETURN
    PreviousClosingBalance + PreviousDailyNetBalance

    If the above one can't help you, please provide some raw data in your table  'GL_Account' and 'GL_Period' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? If yes, please provide the related info. It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Good morning Anonymous 
Please try the following. Probably won't work and sure not optimum but there is a chance that it would. If not, please let me know if we can connect on Saturday (any time before 2:00pm Dubai time)

DailyClosingBalance =
VAR CurrentDate =
    SELECTEDVALUE ( 'GL_Account'[glt_trdate] )
VAR CurrentPeriod =
    SELECTEDVALUE ( 'GL_Period'[id_glperiod] )
VAR PreviousPeriod =
    MAXX (
        FILTER (
            ALLSELECTED ( 'GL_Period'[id_glperiod] ),
            'GL_Period'[id_glperiod] < CurrentPeriod
        ),
        'GL_Period'[id_glperiod]
    )
VAR PreviousClosingBalance =
    CALCULATE (
        MAX ( 'GL_Account'[ClosingBalance] ),
        'GL_Period'[id_glperiod] = PreviousPeriod,
        ALL ( 'Date' )
    )
VAR CurrentPeriodTable =
    CALCULATETABLE ( 'GL_Period', ALL ( 'Date' ) )
VAR TableOnAndBefore =
    FILTER ( CurrentPeriodTable, 'GL_Period'[glt_trdate] <= CurrentDate )
VAR PreviousDailyNetBalance =
    SUMX ( CurrentPeriodTable, 'GL_Account'[Daily Net Balance] )
RETURN
    PreviousClosingBalance + PreviousDailyNetBalance
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi tamerj1 ,

 

Thanks again for your help, it's really appreciated. I added SELECTEDVALUE to the 'GL_Period'[glt_trdate] in the TableOnAndBefore variable but it has returned the same values as that in the Daily Net Balance measure.

 

Unfortunately I cannot share much more information, i.e. have a call with you/sample file as the overwhelming majority of the dataset contains confidental information, but is there anything I can do to help with reworking the DAX to help you in any way? Thank you

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Anonymous 

    no worries I understand. 

    What results did you get without using SELECTEDVALUE? What is the reason for adding SELECTEDVALUE?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi tamerj1 ,

       

      The DAX says "Cannot find name [glt_trdate]" with the following message when the measure is added to the table:

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Anonymous 

        Oh! My mistake. Please try

        DailyClosingBalance =
        VAR CurrentDate =
        SELECTEDVALUE ( 'GL_Account'[glt_trdate] )
        VAR CurrentPeriod =
        SELECTEDVALUE ( 'GL_Period'[id_glperiod] )
        VAR PreviousPeriod =
        MAXX (
        FILTER (
        ALLSELECTED ( 'GL_Period'[id_glperiod] ),
        'GL_Period'[id_glperiod] < CurrentPeriod
        ),
        'GL_Period'[id_glperiod]
        )
        VAR PreviousClosingBalance =
        CALCULATE (
        MAX ( 'GL_Account'[ClosingBalance] ),
        'GL_Period'[id_glperiod] = PreviousPeriod,
        ALL ( 'Date' )
        )
        VAR CurrentPeriodTable =
        CALCULATETABLE ( 'GL_Account', ALL ( 'Date' ) )
        VAR TableOnAndBefore =
        FILTER ( CurrentPeriodTable, 'GL_Account'[glt_trdate] <= CurrentDate )
        VAR PreviousDailyNetBalance =
        SUMX ( CurrentPeriodTable, 'GL_Account'[Daily Net Balance] )
        RETURN
        PreviousClosingBalance + PreviousDailyNetBalance