Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
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
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?
Hi @Anonymous,
You can share your .pbix file by private message.
Thanks,
Angelia
Hi,
I sent you a private message but I didn't see the option to include a file?
Hi @Anonymous,
You can post your .pbix file to OneDrive, and share the link in private message.
Best Regards,
Angelia
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |