Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I have 3 table :
* Sales table including invoice date
* collection table including clearing invoice date, net due date, Collection Period as calculated column (clearing date-Net due date)
* date table with a link between invoice date[sales Table] and clearing date [Collection Table]
Objective : for each row in the sales table I want to include average collection period for previous year
example : if in a row of sales table, invoice date is 31-12-2022, I want to get Average collection period for all invoices cleared from 31-12-2022 to 31-12-2021.
Measure should be -> Average collection previous year = CALCULATE(
base on this measure, how can I include the value in each raw (as calculuted column) in sales table ????
thanks you in advance
Solved! Go to Solution.
Hi @Mseddi
Please try
Average collection previous year =
VAR InvoiceDate = 'Sales'[Date invoice]
RETURN
AVERAGEX (
FILTER (
'Collection',
'Collection'[Date] <= InvoiceDate
&& 'Collection'[Date] >= InvoiceDate - 365
),
'Collection'[Collection Period]
)
Hi @Mseddi
Please try
Average collection previous year =
VAR InvoiceDate = 'Sales'[Date invoice]
RETURN
AVERAGEX (
FILTER (
'Collection',
'Collection'[Date] <= InvoiceDate
&& 'Collection'[Date] >= InvoiceDate - 365
),
'Collection'[Collection Period]
)
If this is being executed as a calculated column, wouldnt the row context restrict the Filter to just that row?
Of course not. This is a No CALCULATE solution which means no context transition.
Yah, my bad. I was assuming you called a measure & it had an implicit calculate.
Thank you guys so much. It worked.
the X is the key for the solution : Row context. ❤️
You will need to use ALL/RemoveFilter in your calculate to remove the filter context of the current row you are on.
If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!