Stop measure results after a specific date

[music43]

Hi

I am playing with the PBI Desktop (finance) dummy data to try and learn DAX. I am trying to stop Sales PY measure from returning a result after the last Sale date. I am pretty certain this DAX should work, which makes wonder if the problem is in the data/model.

Sales table

Budget table (added by me - not in original dataset)

Just in case you need to see, my [Sales Amt] measure:

Sales Amt =

SUMX(

    Sales,

    Sales[Units Sold] * Sales[Sale Price]

)

Can someone please help?

[n8ball]

This is the way I do it. Add a DatesInThePast True / False column to your Date Table. 

Sales Amt PY = 
CALCULATE (
    [Sales Amt],
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Date'[Date] ),
        'Date'[DatesInThePast] = TRUE
    )
)

[music43]

@n8ball Thanks for the suggestion. I will check it out.

[n8ball]

If you want to know more about why I'm using this pattern check out this very good article from SQLBI: Hiding future dates for calculations in DAX - SQLBI

[music43]

Thanks again @n8ball. So many resources, it's difficult to keep track. Especially for someone starting out.
I would give you Kudos but I can't see an option for that. Has it been removed?

[tamerj1]

@music43 

VAR LastSalesDate =
CALCULATE (

    MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) 

)

[music43]

Hi @tamerj1 

I tried what you suggested, but get the same result

Interestingly, the MAX( Sales[Date] ) seems to be returning what I was expecting.

[tamerj1 · Accepted solution]

@music43 

Actually with my suggestion you should use MAX ( Date[Date] ) as condition not SELECTEDVALUE. 

What is happening hear is that SELECTEDVALUE returns blank everywhere while MAX ( Sales[Date] ) returns blank when there is no sale which did the job (blank = blank). But it won't work if you are slicing by Date instead of MonthYear. 

[music43]

@tamerj1 That did the trick - thanks