Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good day, Community.
I am working on a project where I need to calculate the difference in [Cycles] for like [Registration] values for each previous instance of an event in a table, where the order of events is based on the [PRIMARYKEY] of the table. The first instance of each can be left blank.
I have worked most of this out in a calculated column, but keep running into the issue of using like registration values only. It seemed simple to me at first, but I am missing something. Any help is greatly appreicated. Below is a link to some sample data. I have included a screenshot as well.
Solved! Go to Solution.
Disregard... Just got it to work. Thank you for looking, though! Leaving this hear in case anyone else could use.
Cycles Since Previous =
VAR CurrentReg = 'Table'[Registration]
VAR CurrentKEY = 'Table'[PRIMARYKEY]
VAR PrevKEY = CALCULATE(
MAX('Table'[PRIMARYKEY]),
FILTER(
'Table',
'Table'[PRIMARYKEY] < CurrentKEY &&
'Table'[Registration] = CurrentReg
)
)
RETURN
IF(
ISBLANK(PrevKEY),
BLANK(),
'Table'[ Cycles ] -
CALCULATE(
MAX('Table'[ Cycles ]),
FILTER(
'Table',
'Table'[PRIMARYKEY] = PrevKEY
)
))
Disregard... Just got it to work. Thank you for looking, though! Leaving this hear in case anyone else could use.
Cycles Since Previous =
VAR CurrentReg = 'Table'[Registration]
VAR CurrentKEY = 'Table'[PRIMARYKEY]
VAR PrevKEY = CALCULATE(
MAX('Table'[PRIMARYKEY]),
FILTER(
'Table',
'Table'[PRIMARYKEY] < CurrentKEY &&
'Table'[Registration] = CurrentReg
)
)
RETURN
IF(
ISBLANK(PrevKEY),
BLANK(),
'Table'[ Cycles ] -
CALCULATE(
MAX('Table'[ Cycles ]),
FILTER(
'Table',
'Table'[PRIMARYKEY] = PrevKEY
)
))
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |