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

     

     

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