cancel
Showing results 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.

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.

1 ACCEPTED SOLUTION
Super User
3 REPLIES 3
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]))
)
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])``````

Frequent Visitor

Thank you, Daniel!

Super User

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

#### 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