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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

thanks!
and if i wish to add a column of its first purchase channel related to this first purchase date?

Try this

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

Hi my friend,
i have this kind of data table and i wish to build the correct measures/calculations in order to show this kind of bar chart.
i actually need to calculate how many customers were sent X messages successfully.
can you please assist me with the right DAX for it?

thanks in advance.
image.png

is there a way doing this on Power Query and not DAX?

akfir_0-1646946591303.png

 

johnt75
Super User
Super User

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.