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 ...
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] ) )
)

     

     

danextian's avatar
danextian
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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] ) )
)

     

     

    • robderay's avatar
      robderay
      Frequent Visitor
      6 years ago

      I would like to know why is it that if I select FY19 the end of financial year value is different now? I use the formula you mention to hardcode but it seems its only working when I used the FY20 and if i select anything from FY19 the data didnt match? 

       

      Totals EOFY (FY19) = CALCULATE(SUM( Journals[Net Amount]), FILTER(ALL(Dates), Dates[Date] <= DATE(2019,06,30)))