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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors