Getting error using Offset in Time Intelligence

Power Query
1

Hi all,

I am trying to learn how to write Offset time intelligence and getting the error message in the below image. I am using Melissa’s Extended Date table and I have the column Quarter & Year sorted by the column QuarternYear. When I use this in the Offset function, I get the below error when I use the column QuarternYear in the Orderby syntax of the measure.

Can anyone help? Thanks

image
image566×581 13.1 KB

2

Hi @ysherriff,
Thank you very much for posting your question in the forum.
The reason for the error is because we are doing OFFSET on the ALLSELECTED( Date[Quarter & Year] ) table that only has the [Quarter & Year] column and ordering it by the [QuarternYear] column, which does not exist in said table.

You can modify the query to add the [QuarternYear] column to the table returned by ALLSELECTED:

CALCULATE( [# of MQLs],
OFFSET(-1,
ALLSELECTED( Date[Quarter & Year], Date[QuarternYear] ),
ORDERBY( Date[QuarternYear], ASC) ) )

Regards,

1 Like
4

Thank you Jafer for the analysis. It makes sense and it works.

Much appreciated

1 Like