Balance total not showing when there is no data in the filtered year

[Anonymous]

Hello Power BI community! 

I'm trying to show the end balance using the following DAX: 

End balans =

VAR YearSelected = SELECTEDVALUE(TransactionLines[FinancialYear])

    VAR Result =  

    (CALCULATE([Amount],

    FILTER(

        ALL(TransactionLines[FinancialYear]), TransactionLines[FinancialYear] <= YearSelected)))

RETURN

Result

This returns the correct results for most GL accounts, however, if there is no data in the monthly mutation of the current year for a certain GL account it does not give the balance total. Nevertheless, there is data in the past that should be taken into account. For example, here FinancialYear slicer is set to 2022, but the balance total does not show anything for GL 220000 because the amount is booked in 2015 and there are no monthly mutations in 2022.  

I can't base the filter on my DimDate table because then the balance total calculation includes amounts that are booked in the future, whereas I want to limit it to the financial year I'm filtering on. 

Hoping you can help, thank you in advance! 

[Anonymous]

Thank you for the fast response @amitchandak ! 

Unfortunately, my measure fails when I apply that. I'm trying out a new method: first determine the opening balance and then simply sum up the monthly mutations to get the end balance. this gives more accurate results but still the issue of the GLs remains, they do not show the opening balance when there are no monthly mutations: 

Openingbalans =

VAR YearSelected = SELECTEDVALUE(TransactionLines[FinancialYear])

    VAR Result =  

    (CALCULATE([Amount],

    FILTER(

        ALL(TransactionLines[FinancialYear]), TransactionLines[FinancialYear] < YearSelected)))

RETURN

Result

Closing balans = [Begin balans] + [Balans Jan] + [Balans Feb] + [Balans Mrt] + [Balans Apr] + [Balans Mei] + [Balans Jun] + [Balans Jul] + [Balans Aug] + [Balans Sep] + [Balans Okt] + [Balans Nov] + [Balans Dec]

It does show the correct opening balance for all GLs that have a monthly mutation. 

Could you please help me with this, thank you in advance! 

[Anonymous]

Thank you for your fast response! I'll try it out. 

[amitchandak]

@Anonymous , Please create a date/year table and join with and then use measure like

End balans =

VAR YearSelected = SELECTEDVALUE(Date[FinancialYear])

    VAR Result =  

    (CALCULATE([Amount],

    FILTER(

        ALL(Date), Date[FinancialYear] <= YearSelected)))

RETURN

Result

or use the window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc