topic Re: Calculated column of previous value by date & customer in DAX Commands and Tips

Calculated column of previous value by date & customer

akfir — Thu, 10 Mar 2022 14:02:05 GMT

Hi guys,
i wish to add a calculated column as in E column above which calculates the previous grade (by date and customer). please note that in my table i have rows which do not hold a grade and presenting blank value.

Re: Calculated column of previous value by date & customer

johnt75 — Thu, 10 Mar 2022 15:39:25 GMT

Prev grade = 
var currentDate = 'Grades'[Date]
return SELECTCOLUMNS( CALCULATETABLE( 
TOPN( 1, 'Grades', 'Grades'[Date]), ALLEXCEPT(Grades,Grades[Customer code]),
'Grades'[Date] < currentDate ), 
"@prev grade", 'Grades'[Grade] )

Create this as a new column

Re: Calculated column of previous value by date & customer

tamerj1 — Thu, 10 Mar 2022 16:52:05 GMT

Hi akfir

you can try

Previous Grade = VAR CurrentDate = 'Table'[Date] VAR T1 = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer Code] ), 'Table'[Date] < CurrentDate ) VAR T2 = TOPN ( 1, 'T1', [Date] ) VAR T3 = SELECTCOLUMNS ( 'T2', "@PGrade", [Grade] ) RETURN Result

Re: Calculated column of previous value by date & customer

tamerj1 — Thu, 10 Mar 2022 17:07:50 GMT

akfir

You may also try this

Previous Grade = VAR CurrentDate = 'Table'[Date] VAR T1 = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer Code] ) ) VAR T2 = ADDCOLUMNS ( 'T1', "@Ranking", RANKX ( 'T1', [Date] ) ) VAR T3 = ADDCOLUMNS ( 'T2', "@PGrade", VAR CurrentRank = [@Ranking] RETURN LOOKUPVALUE ( [Grade], [@Ranking], CurrentRank + 1 ) ) VAR T4 = FILTER ( 'T3', [Date] = CurrentDate ) VAR Result = SUMX ( 'T4', [@PGrade] ) RETURN Result

Re: Calculated column of previous value by date & customer

akfir — Thu, 10 Mar 2022 21:09:59 GMT

Re: Calculated column of previous value by date & customer

akfir — Thu, 10 Mar 2022 21:13:12 GMT

there is no RESULT variable to return....

Re: Calculated column of previous value by date & customer

tamerj1 — Thu, 10 Mar 2022 21:19:23 GMT

Previous Grade = VAR CurrentDate = 'Table'[Date] VAR T1 = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer Code] ), 'Table'[Date] < CurrentDate ) VAR T2 = TOPN ( 1, 'T1', [Date] ) VAR Result = SELECTCOLUMNS ( 'T2', "@PGrade", [Grade] ) RETURN Result

Re: Calculated column of previous value by date & customer

akfir — Thu, 10 Mar 2022 21:34:27 GMT

<pi>there's not enough memory to complete this operation. please try again later when there may be more memory available.</pi>

Re: Calculated column of previous value by date & customer

tamerj1 — Thu, 10 Mar 2022 21:51:37 GMT

How big is your table?

Re: Calculated column of previous value by date & customer

akfir — Thu, 10 Mar 2022 21:54:12 GMT

30K rows

Re: Calculated column of previous value by date & customer

tamerj1 — Thu, 10 Mar 2022 22:22:19 GMT

How many columns?

Re: Calculated column of previous value by date & customer

tamerj1 — Thu, 10 Mar 2022 22:30:49 GMT

We may reduce the number of columns if you have too many. For now please try this

Previous Grade = VAR CurrentDate = 'Table'[Date] VAR T1 = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer Code] ) ) VAR T2 = FILTER ( 'T1', [Date] < CurrentDate ) VAR T3 = TOPN ( 1, 'T2', [Date] ) VAR T4 = SELECTCOLUMNS ( 'T3', "@PGrade", [Grade] ) RETURN Result

Re: Calculated column of previous value by date & customer

akfir — Fri, 11 Mar 2022 07:00:01 GMT

Perfect thanks! now i wish to add a column of the date referring to this previous value - in my example above - for the first row i want the value to be 01/02/2022

Re: Calculated column of previous value by date & customer

tamerj1 — Fri, 11 Mar 2022 08:20:36 GMT

Previous Date = VAR CurrentDate = 'Table'[Date] VAR T1 = CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer Code] ) ) VAR T2 = FILTER ( 'T1', [Date] < CurrentDate ) VAR T3 = TOPN ( 1, 'T2', [Date] ) VAR Result = SELECTCOLUMNS ( 'T3', "@Date", [Date] ) RETURN Result

Re: Calculated column of previous value by date & customer

akfir — Fri, 11 Mar 2022 09:07:40 GMT

Awsome!! thanks!

Re: Calculated column of previous value by date & customer

tamerj1 — Fri, 11 Mar 2022 09:14:37 GMT

akfir
If my reply solved your problem please consider marking it as an acceptable solution. Thank you and have a great day!

Re: Calculated column of previous value by date & customer

akfir — Sun, 13 Mar 2022 14:36:40 GMT

Why is that?

Re: Calculated column of previous value by date & customer

akfir — Sun, 13 Mar 2022 14:51:05 GMT

this is something related to T4, because the previous date column that you helped me as well - still valid and not showing any error.

Re: Calculated column of previous value by date & customer

tamerj1 — Sun, 13 Mar 2022 14:56:12 GMT

akfir

That means you have multiple records in the same date. Then you can only aggregate by sum or max/min or otherwise provide more filters that can restrict the result to only one record.

Re: Calculated column of previous value by date & customer

akfir — Sun, 13 Mar 2022 14:59:50 GMT

thats maybe because i transferred DATE from date/time to date. what can i do?