Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akfir
Helper V
Helper V

Calculated column of previous value by date & customer

akfir_0-1646920667536.png

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

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

View solution in original post

 

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

 

View solution in original post

Try this

First Purchase Channel =
CALCULATE (
    VALUES ( Data[Purchase Channel] ),
    ALLEXCEPT ( Data, Data[Customer Code] ),
    Data[Date] = Data[First Purchase Date]
)

View solution in original post

26 REPLIES 26
tamerj1
Super User
Super User

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

 

there is no RESULT variable to return....

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

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

How big is your table?

30K rows

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_0-1647182164569.png

Why is that? 

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

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

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

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

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

 

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

 

Awsome!! thanks!

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

How many columns?

@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

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

Hi @akfir 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors