Date hierarchy offset formula incorrect output at month, quarter and year level using Inscope

[ysherriff]

Hi all,

I am creating a date hierarchy offset for time intelligence but for some reason, it works at the day level but at the month , quarter and year level the offset function is not returning the proper value.

Can someone direct me to what the issue is?

Image #1 - Date hierarchy offset formula

Image #2 - Incorrect outuput at month, quarter and year level

[AlexisOlson · Accepted solution]

Your DAX is using 'Date'[CurrDayOffset] inside FILTER for all cases. It should match the period you're calculating for.

[ysherriff]

Duh. I am an idiot. Thanks Alexis. That is exactly the issue......

[amitchandak]

@ysherriff , In this video I have applied the logic of isinscope and offset, check that out, if that can help

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

[ysherriff]

Thanks Amit. I have used your video as a starting point. It is saved in my favorites in Youtube.

Your videos are awesome learning tools.