topic Re: Should I use temp table in my measures to find the median value? in DAX Commands and Tips

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

AlohaPaul — Thu, 07 Oct 2021 21:49:24 GMT

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.

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

wdx223_Daniel — Fri, 08 Oct 2021 02:55:05 GMT

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

AlohaPaul — Sat, 09 Oct 2021 19:48:56 GMT

Thank you, Daniel!

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

AlohaPaul — Sat, 09 Oct 2021 19:53:01 GMT

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])