How to shorten my measures

[kristenk14]

Good Day, I have the following to measures, the one is used within the other, how can these measures be made more efficiently  and maybe put ito 1 measure,

The first measure determines the date of my opening balance

opening_balance_date =

CALCULATE(

    max(investor_flows[date]),

    FILTER(

        ALL(investor_flows),

        MAXX(

            FILTER(

                investor_flows,

                EARLIER(investor_flows[date]) < STARTOFMONTH(investor_flows[date]) &&

                EARLIER(investor_flows[fin_account_id]) = investor_flows[fin_account_id] &&

                EARLIER(investor_flows[flow_type]) == "Balance"

            ),

            investor_flows[date]

        )

    )

)

This one returns te corresponding value of that date

opening_balance =

CALCULATE(

    sum(investor_flows[value]),

    FILTER(

        ALL(investor_flows),

        SUMX(

            FILTER(

                investor_flows,

                EARLIER(investor_flows[date]) = [opening_balance_date] &&

                EARLIER(investor_flows[fin_account_id]) = investor_flows[fin_account_id] &&

                EARLIER(investor_flows[flow_type]) == "Balance"

            ),

            investor_flows[value]

        )

    )

)

[kristenk14]

@amitchandak ,

I tried the above mentioned measure, and it basically gave my closing balance. It did the same as this measure I used to calculate my closing balance 

closing_balance (EoM) = CALCULATE([flow_value], LASTDATE(investor_flows[date]), investor_flows[flow_type] = "Balance")

I want this to be carried over as next month's opening balance. OPENINBALANCEMONTH() doesn't work. The 2 measures above worked but the report performance is very slow.

I managed to reduce the opening balance measure to 

test =

CALCULATE(

    [closing_balance (EoM)],

    FILTER(

        ALL(investor_flows),

        SUMX(

            FILTER(

                investor_flows,

                EARLIER(investor_flows[date]) < STARTOFMONTH(investor_flows[date]) &&

                EARLIER(investor_flows[fin_account_id]) = investor_flows[fin_account_id] &&

                EARLIER(investor_flows[flow_type]) == "Balance"

            ),

            [closing_balance (EoM)]

        )

    )

)

, But report is very slow.

[Anonymous]

Hi @kristenk14 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Best regards,
Community Support Team_ Binbin Yu

[amitchandak]

@kristenk14 , using a date table try a measure like

Sumx( values(Date[Month] ) ,
calculate(firstnonblankvalue(Date[Date], Sum(investor_flows[value]) ), Filter(all(Date), Date[Month] = max(Date[Month])), Filter(investor_flows , investor_flows[flow_type] = "Balance")) )

refer if needed

https://youtu.be/6lzYOXI5wfo