Offset Time Intelligence formula not working

DAX DAX Calculations
1

Hi all,

I am trying to figure out why the discrepancy in the output using Offset vs. Non-offset time intelligence.

Take for instance, Year-to-Date offset formula vs. DatesYTD formula. Why are the values different? See output in image #3. What am I doing wrong or missing? Thanks in advance.

Using Offset
image

Non-offset
image

Outcomes

image
image1298Ă—729 35.6 KB

WIP-EDNA - Influenced.pbix (8.5 MB)

2

Hello @ysherriff,

Thank You for posting your query onto the Forum.

In order to achieve the same results using “Offsets”. Below are the measures alongwith the screenshot of the final results provided -

Current Month-to-Date - Harsh =
SUMX(
    ADDCOLUMNS(
        FILTER( ALL( 'Date'[CurrMonthOffset] ) , 
            'Date'[CurrMonthOffset] = MAX( 'Date'[CurrMonthOffset] ) ) , 
        "@Total_Sales_MTD" , 
        SELECTEDMEASURE() ) , 
    [@Total_Sales_MTD] )

Current Quarter-to-Date - Harsh = 
SUMX(
    ADDCOLUMNS(
        FILTER( ALL( 'Date'[CurrQuarterOffset] ) , 
            'Date'[CurrQuarterOffset] = MAX( 'Date'[CurrQuarterOffset] ) ) , 
        "@Total_Sales_MTD" , 
        SELECTEDMEASURE() ) , 
    [@Total_Sales_MTD] )

Current Year-to-Date - Harsh =
SUMX(
    ADDCOLUMNS(
        FILTER( ALL( 'Date'[CurrYearOffset] ) , 
            'Date'[CurrYearOffset] = MAX( 'Date'[CurrYearOffset] ) ) , 
        "@Total_Sales_MTD" , 
        SELECTEDMEASURE() ) , 
    [@Total_Sales_MTD] )

Final Results
Final Results1518Ă—364 15.5 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

WIP-EDNA - Influenced - Harsh.pbix (8.5 MB)

1 Like
3

Hello @ysherriff

Did the responses from @Harsh help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark his answer as the SOLUTION.

Thank you

4

Thank you Harsh. Very much appreciated.

I will review the syntax.

5

Hello @ysherriff ,

How was it? did the response from Harsh work for you?
If it does, kindly mark as the solution the answer that solved your query.

If not, how far did you get and what kind of help do you need further?

1 Like
6

Hello @ysherriff ,

Just following up if the response above help you solve your inquiry.
If it did, please mark Harsh answer as the SOLUTION.

We’ve noticed that no response was received from you on the post above. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.