Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
66 | |
46 | |
20 | |
19 | |
15 |
User | Count |
---|---|
121 | |
41 | |
38 | |
28 | |
23 |