cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Power BI Fabric Summit Carousel

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