Optimizing Power BI DAX for Historical Receivables Calculations

Hi everyone,

I'm working on a Power BI dashboard to analyze historical receivables data from a Navision database. I've created a calendar table with fiscal years starting on July 1st and ending on June 30th, and I'm using DAX measures to calculate various metrics.

My current approach involves:

• Creating a calculated column to determine if an invoice is open based on posting and closing dates.
• Calculating the total receivables for a given date using CALCULATE and FILTER expressions.
• Calculating the due amounts for a specific period using similar expressions.
• Calculating the receivables for the end of the month and the previous fiscal year using variables and CALCULATE.
• Calculating the overdue rate as a percentage of total receivables.

However, I'm facing performance issues with my visuals, particularly when comparing data across fiscal years.

I'm wondering if there are any optimizations or alternative approaches I could consider.

Here are my DAX formulas:

Is_Invoice_Open = 
IF(
    MAX(customer_ledger_entries[dcl_posting_date]) <= SELECTEDVALUE('Calendar'[Date]) &&
    (
        MAX(customer_ledger_entries[closing_date]) > SELECTEDVALUE('Calendar'[Date]) ||
        MAX(customer_ledger_entries[closing_date]) = DATE(1753, 1, 1)
    ),
    TRUE,
    FALSE
)

sum_total_receivables_date = 
CALCULATE(
    SUM(customer_ledger_entries[amount]),
    FILTER(
        customer_ledger_entries,
        customer_ledger_entries[Is_Invoice_Open] = TRUE
    )
)

sum_due_over_period = 
CALCULATE(
    SUM(customer_ledger_entries[amount]),
    FILTER(
        customer_ledger_entries,
        customer_ledger_entries[Is_Invoice_Open] = True &&
        customer_ledger_entries[age_receivables] >=0
    )
)

receivables_end_month = 
VAR _selectedEndDate = EOMONTH(MAX('Calendar'[Date]), 0)
VAR _currentYear = SELECTEDVALUE('Calendar'[Fiscal_Year])
RETURN

CALCULATE([sum_total_receivables_date], 'Calendar'[Date]=_selectedEndDate, 'Calendar'[Fiscal_Year] = _currentYear
)

receivables_end_month_previous_fy = 
VAR _currentFiscalYear = SELECTEDVALUE ('Calendar'[Fiscal_Year])
VAR _previousFiscalYearStart = VALUE(LEFT(_currentFiscalYear, 4))-1
VAR _previousFiscalYearEnd = VALUE(RIGHT(_currentFiscalYear, 4))- 1
VAR _isSecondHalfOfFiscalYear = MONTH(MAX ( 'Calendar'[Date] ) ) > 6 -- Determine the correct fiscal year and corresponding end-of-month date
VAR _previousFiscalYear = _previousFiscalYearStart & "/" & _previousFiscalYearEnd
VAR _selectedEndOfMonthDate =
    EOMONTH (
        DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ) - 1, MONTH ( MAX ( 'Calendar'[Date] ) ), 1 ),
        0
    )
RETURN
    CALCULATE (
        [sum_total_receivables_date],
        'Calendar'[Date] = _selectedEndOfMonthDate,
        'Calendar'[Fiscal_Year] = _previousFiscalYear
    )

overdue_amount_day = 
VAR _sumdue = [sum_due_over_period]
VAR _sumTotalReceivables = [sum_total_receivables_date]
RETURN

IF(_sumTotalReceivables <> 0, 
    DIVIDE(_sumDue, _sumTotalReceivables, 0), 
    BLANK()
)

Any suggestions or best practices would be greatly appreciated!

Thanks!