cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors