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
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).
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.
Solved! Go to Solution.
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])
Thank you, Daniel!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
29 | |
14 | |
11 | |
10 | |
9 |