Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

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

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.






Super User
Super User

Frequent Visitor

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", 
                                            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) )
MEDIANX(table_age_inMonth, [Ar_age_EOM])


Frequent Visitor

Thank you, Daniel!

Super User
Super User



Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors