cancel
Showing results for
Did you mean:
Helper IV

## Calculated column of previous value by date & customer

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.

3 ACCEPTED SOLUTIONS
Community Champion

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 =
RETURN
Result``````
Community Champion

``````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``````

Community Champion

Try this

``````First Purchase Channel =
CALCULATE (
VALUES ( Data[Purchase Channel] ),
ALLEXCEPT ( Data, Data[Customer Code] ),
Data[Date] = Data[First Purchase Date]
)``````
26 REPLIES 26
Community Champion

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 =
RETURN
Result``````

Helper IV

there is no RESULT variable to return....

Community Champion
``````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 =
RETURN
Result``````
Helper IV

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

Community Champion

Helper IV

30K rows

Community Champion

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 =
RETURN
Result``````
Helper IV

Why is that?

Helper IV

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

Community Champion

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.

Helper IV

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

Community Champion

You have to restore the date time format in order maitain accuracy

Helper IV

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

Community Champion

``````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``````

Helper IV

Awsome!! thanks!

Community Champion

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

Community Champion

How many columns?

Community Champion

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 =
'T2',
VAR CurrentRank = [@Ranking]
RETURN
LOOKUPVALUE ( [Grade], [@Ranking], CurrentRank + 1 )
)
VAR T4 =
FILTER ( 'T3', [Date] = CurrentDate )
VAR Result =
RETURN
Result``````
Helper IV

Hi my friend,
can you please help me build a column of the first purchase date by customer? as attached...

Community Champion

Hi @akfir

``````First Purchase Date =
CALCULATE (
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Customer Code] )
)``````

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!