DAX formula to return fields from the previous day

[Anonymous]

Given this table:

AccountID	Date	Val1
AC1	31 Jan 2020	-0.07
AC1	1 Feb 2020	0.3
AC1	2 Feb 2020	0.03
AC1	3 Feb 2020	0.93

I want a DAX command that adds a column PrevDay and populates it with the previous day's value for Val1, e.g.:

AccountID	Date	Val1	PrevDay
AC1	31 Jan 2020	-0.07
AC1	1 Feb 2020	0.3	-0.07
AC1	2 Feb 2020	0.3	0.3
AC1	3 Feb 2020	0.93	0.3

I have tried the below, but it gives an error "Table variable 'tmp1' cannot be used in current context because a base table is expected."

define
var tmp1 =
SELECTCOLUMNS(
DataTable("AccountID", STRING, "Date", DATETIME, "Val1", DOUBLE,
{
{"AC1","1/31/2020", -0.07},
{"AC1","2/1/2020", 0.3},
{"AC1","2/2/2020", 0.3},
{"AC1","2/3/2020", 0.93}
}
),
"AccountID", [AccountID],
"Date", [Date],
"Val1", [Val1]
)

evaluate
ADDCOLUMNS(
tmp1,
"PrevDay", CALCULATE(SUM(tmp1[Val1]), PREVIOUSDAY(tmp1[Date]))
)

[Anonymous]

Is it essantial to solve it in the report with DAX? I would build that colomn with Power Query in the load step. Probably the easiest way with Power Query should be double that table substract the date by one rename val1 to PrevDate and merge it again on the date (if the date is unique) .

Best Regards

Bruening

[Anonymous]

Yes, it has to be a DAX measure. This is for a tabular model project.

[nandukrishnavs · Accepted solution]

@Anonymous ,

Try below-calculated column.

PreviousVal = 
var __PreviousDay= CALCULATE(PREVIOUSDAY(Query2[DateVal]))
var __PreviousVal=LOOKUPVALUE(Query2[Val1],Query2[DateVal], __PreviousDay)
return __PreviousVal

[Anonymous]

While that formula works, it is extremely slow. When I build a measure out of this formula, it takes 90 seconds to calculate (the table has 500,000 accounts, each with 31 rows per month).

Are there any more efficient ways to do this?

Perhaps doing a JOIN onto the previous day and do a SUMX on that resulting table, rather than LOOKUPVALUE inside the SUMX?

[Anonymous]

The formula given by @nandukrishnavs is slow for 2 reasons:

1. CALCULATE executes context transition and this is an expensive operation and should never be attempted on a fact table row by row. NEVER.

2. LOOKUPVALUE is extremely slow and should only be used for small dimensions. NEVER on a fact table.

The thing you want to do should be performed in Power Query or in the source, not in DAX. DAX is a Data Analysis eXpressions language, not a data mashup language (like M). Use Power Query or the language your source uses (probably SQL).

Best
D

[Anonymous]

Is there a faster way to do this measure in DAX without having to add extra fields in the datawarehouse tables? 

This is a bit disappointing, as I thought that a tabular model was designed to query large sets of data more efficiently than a traditional relational database.

And I thought DAX had special functions specifically to handle time-intelligence, which is what this problem is.

Instead of LOOKUPVALUE, should I use PREVIOUSDAY? I have tried this in a DAX measure

CALCULATE(SUM('AccountDaily'[Val1]), PREVIOUSDAY('AccountDaily'[Date]))

but it returns blank for every row. What am I doing wrong?

When a user selects the month of February in a filter, is the PREVIOUSDAY function able to return a date of 31 Jan? Or is it limited by the entire set of dates passed in by the filter?

[Anonymous]

DAX is blazingly fast if CORRECTLY CODED and if the model is CORRECT. I'm afraid this is not the case in this instance.

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D

[Anonymous]

You can try this but the assumption must be true for it to work correctly.

// The assumption is that there are no
// gaps in days for each and every account
// in the data and there is only one record
// for each day for each account.

PreviousVal = 
var __currentDay = Query2[DateVal]
var __account = Query2[AccountID]
var __prevVal =
	MAXX(
		filter(
			Query2,
			Query2[DateVal] = __currentDay - 1
			&&
			Query2[AccountID] = __account
		),
		Query2[Val1]
	)
return
	__prevVal

It will be much faster (or at least should be) than the one given by @nandukrishnavs because it does not use CALCULATE and LOOKUPVALUE.

Best

D

[Anonymous]

Thanks for the reply, @Anonymous.

When I try this method in the code below, the PrevVal1 column is always blank. Any idea why?

define
var tmp1 =
DataTable("AccountID", STRING, "Date", DATETIME, "Val1", DOUBLE,
{
{"AC1","1/31/2020", -0.07},
{"AC1","2/1/2020", 0.3},
{"AC1","2/2/2020", 0.3},
{"AC1","2/3/2020", 0.93}
}
)

evaluate
ADDCOLUMNS(
tmp1,
"PrevVal",
VAR _account = [AccountID]
VAR _currentday = [Date]
VAR _prevVal1 = MAXX(FILTER(tmp1, [AccountID] = _account && [Date] = _currentday-1), [Val1])
RETURN _prevVal1
)

[Anonymous]

Works perfectly well. Paste this into Dax Studio and run.

evaluate
	var __tmpTable =
	SELECTCOLUMNS(
		{
			("AC1", date(2020, 1, 31), -0.07),
			("AC1", date(2020, 2, 1), 0.3),
			("AC1", date(2020, 2, 2), 0.3),
			("AC1", date(2020, 2, 3), 0.93)
		},
		"AccountID", [Value1],
		"Date", [Value2],
		"Val1", [Value3]
	)
	return	
		ADDCOLUMNS(
			__tmpTable,
			"PrevVal",
				VAR __account = [AccountID]
				VAR __currentday = [Date]
				VAR __prevVal1 = 
					MAXX(
						FILTER(
							__tmpTable,
							[AccountID] = __account
							&& 
							[Date] = __currentday - 1
						),
						[Val1]
					)
				RETURN 
					__prevVal1
		)

Best

D

[Anonymous]

When I run this, I get the error:

Query (4, 3) Operator or expression '{ }' is not supported in this context.

[Anonymous]

You are not using the latest version of DAX.

Best
D

[Anonymous]

Ok, the issue with the DATATABLE was that I needed to use "2020-01-31" format instead of "31/1/2020" format. Using the correct format does give the data for yesterday.

Now, when I run this for the entire table in the cube, it takes 234 seconds to calculate. The LOOKUPVALUE method takes 128 seconds to calculate. Both times are still too slow to be used on a user report.

When I do the equivalent query in SQL (using a LEFT JOIN to get previous days data in a set-based query), it takes only 6 seconds.

How would I go about doing the JOINs using DAX functions?

[Anonymous]

You're a bit out of luck if you want to do this in DAX. "Get better hardware" is you best option right now. Or reduce the amount of data.

Such calculations should always be performed either at the source or in Power Query. DAX is your last resort and you can't do much to speed it up. DAX is not a data mashup engine.

Best
D

[Anonymous]

I've accepted @nandukrishnavs's method as the solution, as that did indeed do what I asked. The rest of my issues were to do with performance and design. Thanks very much to @Anonymous for your input.

I've used the sugestions and come up with a measure that is working, and is a lot faster. It takes 4 seconds to calculate for the entire unfiltered dataset, which is quite acceptable, and about a second when the user selects any particular month. I've posted it here in case it's useful to anyone else.

I think the key to this performing better than earlier suggestions in this thread is that it first uses SUMMARIZE to get a distinct list of (AccountID,TranDate). This helps because most accounts only have 1 or 2 transactions per month, so the subsequent LOOKUPVALUE only executes for those dates it needs to (probably about 10 to 15 times fewer calls).

ScoreTxLimit = 
SUMX(
      ADDCOLUMNS(
                SUMMARIZE(
                    'Transaction',
                    'Transaction'[AccountID],
                    'Transaction'[TranDate]
                ),
                "ScorePrevDay",
                    LOOKUPVALUE(
                        'AccountDaily'[Score],
                        'AccountDaily'[AccountID], [AccountID],
                        'AccountDaily'[TranDate], PREVIOUSDAY('Transaction'[TranDate])
                    ),
                "LimitPrevDay",
                    LOOKUPVALUE(
                        'AccountDaily'[Limit],
                        'AccountDaily'[AccountID], [AccountID],
                        'AccountDaily'[TranDate], PREVIOUSDAY('Transaction'[TranDate])
                    )
            ),
        IF([ScorePrevDay] >= 0.01 && [ScorePrevDay] <= 2.99, MIN([Transaction Total], [LimitPrevDay]))
    )

[Anonymous]

One last thing... Do you realize that the function PREVIOUSDAY does not return the previous day if the previous day does not exist in the column you've given it but the first day that precedes the day in question? I just do hope you know how this function works on a column that does not feature contiguous dates... and it's, in fact, the behavior you're after.

Best
D

[Anonymous]

You're quite right. While iterating, if the SUMX encounters a row with a TranDate of, say, 15 Jan 2020, and the Transaction table contains no rows with TranDate 14 Jan 2020, then PREVIOUSDAY([TranDate]) will not return 14 Jan 2020, but will return BLANK.

DATEADD([TranDate], -1, DAY) will also return BLANK.

However, [Date] -1 does return 14 Jan 2020.

For my measure, I would still want to look up the values in AccountDaily for 14 Jan 2020 for the AccountID, so I should be using [TranDate] -1. The Transaction table contains around 30000 records every single day, so it is very unlikely that any [TranDate] would be missing - but it is the more correct method.