Forum Discussion

robderay's avatar
robderay
Frequent Visitor
6 years ago
Solved

Balance Sheet

Hi Team - I would like to know any suggestions you have to get a specified balance sheet totals for specified date (especially looking at end of financial year "30-06-2019")? I've got certain matrix visual with columns for balance sheet total where you can have a slicer for FY and Monthly selection. However, I can't seem to get the right number for balance sheet for end of financial year when i select FY20 and another selection for month. 

 

Here's the formula I used: 

 

Totals EOFY (FY19) =
VAR MinDateInDateTable = CALCULATE (
MIN ( Dates[Date] ),
ALL( Dates )
)

Return CALCULATE([Journal Lines Total], FILTER(ALL(Dates), Dates[Date] >= MinDateInDateTable &&
FORMAT(Dates[Date], "dd/mm/yyyy") < "01/06/2019"))
Need Help
  • danextian's avatar
    danextian
    6 years ago

    Hello,

     

    For prior financial year end balance, you may try hardcoding the actual date such as below:

    CALCULATE (
    SUM ( Fact[Amount] ),
    FILTER ( ALL ( DatesTable ), DatesTable[Date] <= DATE(2019, 1, 1 ) )
)

     

    Or to calculate the running balance before the current financial year in the current filter context,  use

    CALCULATE (
    SUM ( Fact[Amount] ),
    FILTER ( ALL ( DatesTable ), DatesTable[FinancialYear] < MIN( DatesTable[FinancialYear] ) )
)

     

     

4 Replies

  • danextian's avatar
    danextian
    Super User
    6 years ago

    Hi robderay ,

     

    The Balance Sheet balance  the running total of a real account at a given day. Try these formulas:

    Balance =
//use if you have separate dates table
CALCULATE (
    SUM ( Fact[Amount] ),
    FILTER ( ALL ( DatesTable ), DatesTable[Date] <= MAX ( DatesTable[Date] ) )
)

     

    Balance =
//if you don't have a separate  dates table
CALCULATE (
    SUM ( Fact[Amount] ),
    FILTER (
        ALL ( Fact[JournalDate] ),
        Fact[JournalDate] <= MAX ( Fact[JournalDate] )
    )
)


    These formulas will give you the running total for a give date or if you use month in a visual, the max date in that month, regardless of whether you are following calendar or fiscal year.

    • robderay's avatar
      robderay
      Frequent Visitor
      6 years ago

      hi danextian - 

       

      Thanks. I think that was the formula used for the balance sheet from earliest to latest date selected? Which I already used for current balance sheet formula. What I'm looking at is to get a certain balance sheet totals for end of financial year last year on the matrix as well. This is for me to get the variance of any selected month and the totals for end of FY last year ("30-06-2019" for e.g.). 

      • danextian's avatar
        danextian
        Super User
        6 years ago

        Hello,

         

        For prior financial year end balance, you may try hardcoding the actual date such as below:

        CALCULATE (
    SUM ( Fact[Amount] ),
    FILTER ( ALL ( DatesTable ), DatesTable[Date] <= DATE(2019, 1, 1 ) )
)

         

        Or to calculate the running balance before the current financial year in the current filter context,  use

        CALCULATE (
    SUM ( Fact[Amount] ),
    FILTER ( ALL ( DatesTable ), DatesTable[FinancialYear] < MIN( DatesTable[FinancialYear] ) )
)