Calculated column of previous value by date & customer

[akfir]

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.

[tamerj1 · Accepted solution]

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

[tamerj1 · Accepted solution]

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

[tamerj1]

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

[akfir]

there is no RESULT variable to return....

[tamerj1]

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

[akfir]

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

[tamerj1]

How big is your table?

[akfir]

30K rows

[tamerj1 · Accepted solution]

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

[akfir]

Why is that? 

[akfir]

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

[tamerj1]

@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. 

[akfir]

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

[tamerj1]

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

[akfir]

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

[tamerj1 · Accepted solution]

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

[akfir]

Awsome!! thanks!

[tamerj1]

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

[tamerj1]

How many columns?

[tamerj1]

@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

[akfir]

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

[tamerj1]

Hi @akfir 

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