Balance Sheet

[robderay]

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

[danextian]

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]

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 · Accepted solution]

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]

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