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.
I cannot seem to work out the DAX for what should be a simple calculation. Can anyone suggest how I would create a DAX measure to do the below?
Given this table definition: AccountDaily(AccountID, Date, Balance, Score, Purchases)
I need a DAX measure that returns this functionality for a measure called EACC:
When yesterday's Score is between 0.01 and 2.99
then EACC = the smaller of yesterday's Balance and today's Purchases
else
EACC = 0
It is easy in SQL. Here is my SQL query to return the correct value for an example AccountID and Month:
DECLARE @AccountId INT = 12345
DECLARE @Date DATE = '2020-02-01'
SELECT EACC = SUM(
CASE
WHEN Y.Score NOT BETWEEN 0.01 AND 2.99 THEN 0
WHEN Y.Balance < T.Purchases THEN Y.Balance
ELSE T.Purchases
END
)
FROM AccountDay T --Today
LEFT JOIN AccountDay Y --Yesterday
ON T.AccountID = Y.AccountID
AND Y.Date = DATEADD(DAY, -1, T.Date)
WHERE T.AccountID = @AccountId
AND DATEDIFF(MONTH, T.Date, @date) = 0
Here is a set of sample data:
AccountId | Date | Score | Balance | Purchases |
12345 | 31/01/2020 | 0.04 | 73.65 | 0 |
12345 | 01/02/2020 | 0.04 | 73.65 | 180 |
12345 | 02/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 03/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 04/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 05/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 06/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 07/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 08/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 09/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 10/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 11/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 12/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 13/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 14/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 15/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 16/02/2020 | 0.93 | 1486.38 | 0 |
12345 | 17/02/2020 | 0.93 | 1486.38 | 1000 |
12345 | 18/02/2020 | 0.3 | 486.38 | 0 |
12345 | 19/02/2020 | 0.3 | 486.38 | 0 |
12345 | 20/02/2020 | 0.3 | 486.38 | 0 |
12345 | 21/02/2020 | 0.3 | 486.38 | 0 |
12345 | 22/02/2020 | 0.3 | 486.38 | 0 |
12345 | 23/02/2020 | 0.3 | 486.38 | 0 |
12345 | 24/02/2020 | 0.3 | 486.38 | 0 |
12345 | 25/02/2020 | 0.3 | 486.38 | 0 |
12345 | 26/02/2020 | 0.3 | 486.38 | 0 |
12345 | 27/02/2020 | 0.3 | 486.38 | 0 |
12345 | 28/02/2020 | 0.3 | 486.38 | 0 |
12345 | 29/02/2020 | 0.3 | 486.38 | 600 |
Sample data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Yesterday would be Yesterday = (TODAY() - 1) * 1.
Sorry, when I refer to "yesterday" I mean "the day prior to the current day" i.e. the row in AccountDaily where Date = DATEADD(DAY, -1, Date)
MeasureEACC = VAR _yesScore = LOOKUPVALUE(AccountDaily[Score], AccountDaily[AccountId], MIN(AccountDaily[AccountId]), AccountDaily[Date], MIN(AccountDaily[Date]) -1)
VAR _yesBalance = LOOKUPVALUE(AccountDaily[Balance], AccountDaily[AccountId], MIN(AccountDaily[AccountId]), AccountDaily[Date], MIN(AccountDaily[Date]) - 1)
RETURN
if (_yesScore > 0.01 && _yesScore < 2.99,
if (_yesBalance < MIN(AccountDaily[Purchases]), _yesBalance, MIN(AccountDaily[Purchases])),
0)
I think i've got the logic. Please test with edge cases and other Account IDs
HotChilli, your formula works at the daily level. Thank you.
But when I report by month, it gives the wrong number (220.95 instead of 1560.03). At monthly level, the measure needs to sum up the value of the measure from each day.
I think this means I have to wrap up your DAX measure into a SUMX. Here is my attempt to do that:
MeasureEACC =
SUMX(
'AccountDaily',
VAR _yesScore =
LOOKUPVALUE(
'AccountDaily'[Score],
'AccountDaily'[AccountID], MIN('AccountDaily'[AccountID]),
'AccountDaily'[Date], MIN('AccountDaily'[Date]) -1
)
VAR _yesBalance =
LOOKUPVALUE(
'AccountDaily'[Balance],
'AccountDaily'[AccountID], MIN('AccountDaily'[AccountID]),
'AccountDaily'[Date], MIN('AccountDaily'[Date]) -1
)
RETURN
IF(
_yesSccore >= 0.01 && _yesSccore <= 2.99,
MIN(_yesBalance, 'AccountDaily'[Purchases]),
0)
)
I'm not sure what is going wtong here!
If you stick with the original measure I wrote, you can probably create a new measure
NewEACC = SUMX(AccountDaily, [MeasureEACC])
which might work at month level
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |