Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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] )
)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
19 | |
14 | |
11 |
User | Count |
---|---|
43 | |
35 | |
25 | |
22 | |
22 |