cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper V

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
Super User

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

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

Super User

Try this

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

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

Super User

Try this

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

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?

Helper V

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

Helper V

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