DAX formula to look at previous day

[Anonymous]

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

[Greg_Deckler]

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.

[Anonymous]

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)

[HotChilli]

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

[Anonymous]

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!

[HotChilli]

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