Optimizing Power BI DAX for Historical Receivables Calculations

[marie_joy]

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!

[marie_joy]

Hi @some_bih 

Thanks for your reply, in fact it is the DAX queries that takes long: between 30s and 50s. Particularly the 

receivables_end_month, the receivables_end_month_previous_year.

The problem is that I cannot aggregate because I do need the daily values for other pages...

[some_bih]

Hi @marie_joy it seems that your measure receivables_end_month is related to measure sum_total_receivables_date which is related to calculated column is open?
If yes, and you really need this calculated column please be focuse on this column and think how to rewrite it.

[some_bih]

Hi @marie_joy 

Firstly, please use link for performance analyzer (and other links on that link) to figure out what takes so long to retrive visuals. It could be easly calculated column you created cause problems.

If you have daily data, BUT, you need it only on end of month data level think how to aggregate data, if possible, before it is imported into Power BI file.