March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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
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
Try this
First Purchase Channel =
CALCULATE (
VALUES ( Data[Purchase Channel] ),
ALLEXCEPT ( Data, Data[Customer Code] ),
Data[Date] = Data[First Purchase Date]
)
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
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.
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?
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...
Hi @akfir
First Purchase Date =
CALCULATE (
MIN ( 'Table'[Date] ),
ALLEXCEPT ( 'Table', 'Table'[Customer Code] )
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
25 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
39 | |
29 | |
27 | |
20 | |
18 |