Calculate value of 3 months ago doesn't work

[Anonymous]

Hi,

I have this weird issue: I want to calculate the average of a calculated column for the period 3 months ago. So I created this measure:

Value3MonthsAgo = CALCULATE(AVERAGE(Table[CalculatedColumn]); FILTER('Table';Table[Period] = DATEADD(LASTDATE(Table[Period]);-3;MONTH)))

And it only returns a BLANK value...

I tried the same with another column and then it is working:

FakeValue3MonthsAgo = CALCULATE(AVERAGE(Table[OtherNonCalculatedColumn]); FILTER('Table';Table[Period] = DATEADD(LASTDATE(Table[Period]);-3;MONTH)))

And when filtering in min period it is also working:

FakeValue3MonthsAgo = CALCULATE(AVERAGE(Table[CalculatedColumn]); FILTER('Table';Table[Period] = MIN(Table[Period])))

I don't get it.... what do I do wrong?

[v-huizhn-msft]

Hi @Anonymous,

What's the formula of CalculatedColumn used in the formual? Could you please share your sample table or .pbix file for further analysis? Please create a calculate column using the following formula and check if it works fine.

selected date=DATEADD(LASTDATE(Table[Period]);-3;MONTH)

In addition, Date column used in DATEADD function must have a contiguous range of dates. More details, please review this blog(it's still the same in Power BI): https://powerpivotpro.com/2011/10/slicers-for-selecting-last-x-periods/#comment-274090 .

Best Regards,
Angelia

[Anonymous]

Hi,

I don't have a continuous range of dates. Only dates every 3 months. But other measures work, so I don't think that could be the problem...

How do I share a .pbix file here?

[v-huizhn-msft]

Hi @Anonymous,

You can share your .pbix file by private message.

Thanks,
Angelia

[Anonymous]

Hi,

I sent you a private message but I didn't see the option to include a file?

[v-huizhn-msft]

Hi @Anonymous,

You can post your .pbix file to OneDrive, and share the link in private message.

Best Regards,
Angelia

[Anonymous]

@v-huizhn-msft, I sent you a private message but I didn't get any respond?