Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
AlohaPaul
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).  

 

PBI_problem_-_Google_Sheets.png

 

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

PBI_problem_-_Google_Sheets.png

 

Which is the summary of the calculation here

PBI_problem_-_Google_Sheets.png

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
wdx223_Daniel
Super User
Super User

3 REPLIES 3
AlohaPaul
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", 
                                   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
Frequent Visitor

Thank you, Daniel!

wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1633661689914.png

 

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

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