topic Re: DAX formula to look at previous day in DAX Commands and Tips

DAX formula to look at previous day

Anonymous — Tue, 07 Apr 2020 14:46:40 GMT

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

Re: DAX formula to look at previous day

Greg_Deckler — Tue, 07 Apr 2020 14:33:41 GMT

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.

Re: DAX formula to look at previous day

Anonymous — Tue, 07 Apr 2020 14:48:49 GMT

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)

Re: DAX formula to look at previous day

HotChilli — Tue, 07 Apr 2020 17:53:46 GMT

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

Re: DAX formula to look at previous day

Anonymous — Wed, 08 Apr 2020 09:33:34 GMT

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!

Re: DAX formula to look at previous day

HotChilli — Thu, 09 Apr 2020 01:44:10 GMT

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