Should I use temp table in my measures to find the median value?

[AlohaPaul]

Hi PowerBI masters,

Any idea how the DAX formula I need for this report needs below?   It seems that I have to create measures with dynamic temp table in its DAX to solve the problem.   Honestly, I am not sure. 

Thank you all in advance for your help!
I have a table of invoices with its invoice dates and payment dates.   Plus a calculated column: Final AR age (= Payment date - Invoice date).  

I am trying to design a report like this one below:

Which is the summary of the calculation here

For example, for invoice #2, on Aug 31, its AR age = Aug 31 - July 5 = 57.  However,  in September, it is no longer outstanding.  Therefore it is excluded from the median outstanding invoice's AR calculation.

Although I can add all the calculated columns (in blue), I would prefer a more dynamic approach since I like a report for the last 24 months.  But the AR Age for each month-end needs to be calculated dynamically to get the correct median AR age.

 

[AlohaPaul]

Just for future reference, here is my PBI version.  It is very similar to Daniel.

#Age_median = 
var start_of_month = SELECTEDVALUE('_End of Month Dates'[Month Start]) //BoM)
var end_of_month = EOMONTH(start_of_month,0)

var table_unpaid =  //A = invocie date <= end_month , B = payment date > end_monht, C = payment is blank
                    // AB + AC
                    FILTER('Table 1', ('Table 1'[Invoice date]<=end_of_month && 'Table 1'[Payment date] > end_of_month)
                                    || ('Table 1'[Invoice date]<=end_of_month && ISBLANK('Table 1'[Payment date]))
                                    )
var table_age_inMonth = ADDCOLUMNS(table_unpaid, "Ar_age_EOM", 
                                   switch(TRUE(),
                                            ISBLANK([Payment date]), DATEDIFF( [Invoice date], end_of_month, DAY),
                                            [Payment date]>end_of_month, DATEDIFF( [Invoice date], end_of_month, DAY),  
                                            DATEDIFF( [Invoice date],[Payment date], DAY) )
                                    )   
            
return
MEDIANX(table_age_inMonth, [Ar_age_EOM])

[AlohaPaul]

Thank you, Daniel!

[wdx223_Daniel · Accepted solution]