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

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