Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Fund Balance Comparison by Year Formulas

I am trying to map out fund balances overtime and I have the following formula:

 

Running Fund Balances =
CALCULATE(
    [Rev - Exp],
    FILTER(
        ALLSELECTED('0. Calendar'[Month Sort]),
        ISONORAFTER('0. Calendar'[Month Sort], MAX('0. Calendar'[Month Sort]), DESC)
    )
 
) + 'Fund Balances'['22 Beginning Year Cash Balance]
 
The issue I am running into is that I cannot simply use the starting balance for the given year because the '22 starting balance is different than 2021 and 2020 as they each have their own starting balances.  How can I create a bar chart to where if the year is 2021 it will add the 2021 beg balance and if the year is 2022 it will add the '2022 beg balance?  Maybe there is a better way to set-up as well?  
 
I am basically trying to calculate a way to compare ending fund balances of each quarter of the year to where my legend is year and x axis is quarter.
 
I also have the following formulas for each year 
 
'20 Running Fund Balance: =
CALCULATE(TOTALYTD([2020 Actuals :], '0. Calendar'[Date]) + 'Fund Balances'['20 Beginning Year Cash Balance])
 
But when put together on a chart since the date range is capuring the full year past year quarters repeat.
 
 
 
 
 

 

 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Assume you have fund balance column date wise of year start date wise

     

    Then you can have a measure

     

    calculate(firstnonblankvalue('Date'[Date], sum('Fund Balances'[Year Cash Balance])) , filter(all('Date'), 'Date'[Year] = max('Date'[Year]) ))

     

    Else you need use Switch

     

     

    Switch(Year(Max('Date'[Date])),

    2022, 'Fund Balances'['22 Beginning Year Cash Balance]),

    2021, 'Fund Balances'['21 Beginning Year Cash Balance]),

    2020, 'Fund Balances'['20 Beginning Year Cash Balance])

    )

     

     

    Rest is what you are doing or follow

     

    Power BI Inventory On Hand
    Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    My fund balances are not its own column rather it a measure. The thing that is holding it up is I want to have it calculate where it is differentiating by the year.  So if the year is'21 it is using the beg '21 balance to calculate a rolling total based on date for that year.  I guess my question is how can I combine the following three formulas:

     

    '20 Running Fund Balance: =
    CALCULATE(TOTALYTD([2020 Actuals :], '0. Calendar'[Date]) + 'Fund Balances'['20 Beginning Year Cash Balance])
     
    '21 Running Fund Balance: =
    CALCULATE(TOTALYTD([2021 Actuals :], '0. Calendar'[Date]) + 'Fund Balances'['21 Beginning Year Cash Balance])
     
    '22 Running Fund Balance: =
    CALCULATE(TOTALYTD([2022 Actuals :], '0. Calendar'[Date]) + 'Fund Balances'['22 Beginning Year Cash Balance])
     
    To appear in a list where I can use time intelligence to create charts.  because by using this formula
    Running Fund Balances =
    CALCULATE(
        [Rev - Exp],
        FILTER(
            ALLSELECTED('0. Calendar'[Month Sort]),
            ISONORAFTER('0. Calendar'[Month Sort], MAX('0. Calendar'[Month Sort]), DESC)
        )
     
    ) + 'Fund Balances'['22 Beginning Year Cash Balance]
     
    It is adding '22 to '21 which is what I dont want.  Would it help to share file? The end goal is to show clustered column chart to show historial ending fund balances year vs year.