topic Re: Stop measure results after a specific date in DAX Commands and Tips

Stop measure results after a specific date

music43 — Fri, 10 Jun 2022 12:10:49 GMT

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?

Re: Stop measure results after a specific date

tamerj1 — Fri, 10 Jun 2022 12:28:09 GMT

music43

VAR LastSalesDate =
CALCULATE (

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

)

Re: Stop measure results after a specific date

n8ball — Fri, 10 Jun 2022 15:23:20 GMT

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 ) )

Re: Stop measure results after a specific date

music43 — Fri, 10 Jun 2022 15:24:51 GMT

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.

Re: Stop measure results after a specific date

tamerj1 — Fri, 10 Jun 2022 15:39:19 GMT

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.

Re: Stop measure results after a specific date

music43 — Fri, 10 Jun 2022 15:46:48 GMT

tamerj1 That did the trick - thanks

Re: Stop measure results after a specific date

music43 — Fri, 10 Jun 2022 15:47:30 GMT

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

Re: Stop measure results after a specific date

n8ball — Fri, 10 Jun 2022 19:46:56 GMT

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

Re: Stop measure results after a specific date

music43 — Mon, 13 Jun 2022 08:17:33 GMT

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?