Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Can someone please help?
Solved! Go to Solution.
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.
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
)
)
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
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?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |