HELP _ Including measure in calculated column

[Mseddi]

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(

    AVERAGE('Collection'[Collection Period])

    ,PREVIOUSYEAR ('Sales'[Date invoice]))

base on this measure, how can I include the value in each raw (as calculuted column) in sales table ????

thanks you in advance

[tamerj1 · Accepted 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]
)

[AnthonyGenovese]

If this is being executed as a calculated column, wouldnt the row context restrict the Filter to just that row?

[tamerj1]

@AnthonyGenovese 

Of course not. This is a No CALCULATE solution which means no context transition. 

[AnthonyGenovese]

Yah, my bad. I was assuming you called a measure & it had an implicit calculate.

[Mseddi]

Thank you guys so much. It worked.

the X is the key for the solution : Row context. ❤️

[AnthonyGenovese]

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!