DATE ADD Question/problem

[Anonymous]

Hello Community  -  I have a date table.      I have the following measure that sums up the value of orders received (as of yesterday's date).    It works in the sense that is giving me the correct sum for yesterday  (July 4th).    Date table is connected to order date on my orders table.  

You will notice that the measure is grabbing  the Dec 31 2019 date as you can see below.   My desire is to ONLY have the values for 2020 be counted  (and yes, I have a filter selected already for the Year...2020...but the 2019 data is still showing up).  

I think I understand what is happening but not sure how to fix it.    In our June 2020 orders, there is a skipped date between June 13th and June 15th.     So, on June 14th, we must not have received any orders at all.   (not even zero dollar value orders, which we have sometimes).       The measure seems to be skipping back to the next earliest date if there is no value.      So in the case of January 2, 2020....since there were no orders on January 1 2020 it is skipping back Dec 31 2019  (and grabbing 2019 data which I do not want).    I only want 2020 data...but even checking the 2020 filter does not seem to stop the behavior.  

Order Date Yesterday Orders =

CALCULATE (

    SUM ( 'Orders 2'[Net Price] ),DATEADD('Date Table'[Date],-1,DAY)

    )

[mahoneypat · Accepted solution]

It looks like you are using the Order Date column from your fact table i.e., not your Date table.  When you add -1 day to the Date table with DATEADD() you are then returning a result for the day of the previous year (even though the Date table is filtered to the current year).  If you use your 'Date Table'[Date] column in your visual you should take care of the issue.

If you need to keep the order date column in there for some reason, you can add another term to your CALCULATE, so that it is filtered down to the current year.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat     Putting the "dates" column there instead of "order dates" did indeed fix the issue!    Thank you.

But one question:    I thought that since my "order dates" column had a primary active relationship with my dates table, that the two essentially were interchangeable.   I thought that was the point of making something an active relationship?  

[harshnathani]

Hi @Anonymous ,

You can try this measure

Order Date Yesterday Orders =
VAR a =
    MAX ( 'Orders 2'[Order Date] )
VAR previousdate =
    CALCULATE (
        MAX( 'Orders 2'[Order Date] ),
        FILTER (
            ALL ( 'Orders 2' ),
            'Orders 2'[Order Date] < a
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Orders 2'[Net Price] ),
        FILTER (
            ALL ( 'Orders 2' ),
            'Orders 2'[Order Date] = previousdate
        )
    )

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!