Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi. This is a really dumb question. I should be demoted in my powerbi community levels. Let's say I have a table like this -- intentionally not connected to any date table (so I can't use PREVIOUSYEAR(date[date] or the like).
YEAR -- ID -- FTE
2019 -- 23 -- .75
2019 -- 24 -- .6
2020 -- 23 -- 1.1
2020 -- 24 -- 3
Let's say I wanted to add a column that shows simply the prior year's value of FTE such that the table would look like this:
YEAR -- ID -- FTE -- PRIOR YEAR FTE
2019 -- 23 -- .75 -- null
2019 -- 24 -- .6 -- null
2020 -- 23 -- 1.1 --.75
2020 -- 24 -- 3 -- .6
What would be the best way to do this? Rember - I dont have this connected to a date table intentionally (multiple 100m row data table so I'm trying to keep the joins to as few as possible unless critical.)
This may be too much information but if it helps,
(1) the current FTE calc comes from a CALCULATE(sumx(DISTINCT(BigTable[ID]),sum(BigTable[FTE]))
(2) so I tried a simple:
sumx(DISTINCT(BigTable[ID]),sum(BigTable[FTE])),
FILTER (
Solved! Go to Solution.
Prior Year FTE =
MAXX (
FILTER (
'Table',
'Table'[Year]
= EARLIER ( 'Table'[Year] ) - 1
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
),
'Table'[FTE]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@charleshale I am not sure why you wrote FTE like CALCULATE(sumx(DISTINCT(BigTable[ID]),sum(BigTable[FTE]))? You can probably simplify this as follows. When you are dealing with a 100mil dataset, each and every measure you write needs to be performant else you will run into serious issues.
_sum = SUM(BigTable[FTE])
once you have it, you can get previous year some for the same id like following
_prevSum =
VAR _year =
MAX ( BigTable[Year] ) - 1
RETURN
CALCULATE ( [_sum], BigTable[Year] = _year )
@charleshale I am not sure why you wrote FTE like CALCULATE(sumx(DISTINCT(BigTable[ID]),sum(BigTable[FTE]))? You can probably simplify this as follows. When you are dealing with a 100mil dataset, each and every measure you write needs to be performant else you will run into serious issues.
_sum = SUM(BigTable[FTE])
once you have it, you can get previous year some for the same id like following
_prevSum =
VAR _year =
MAX ( BigTable[Year] ) - 1
RETURN
CALCULATE ( [_sum], BigTable[Year] = _year )
Well shoot good catch on the overcomplication of the sumx .
The _prevsum works beautifull Thank you.
Correction: it's now failing to give me the previous year after having worked. Maybe I've a got bug somewhere. This is very strange
Prior Year FTE =
MAXX (
FILTER (
'Table',
'Table'[Year]
= EARLIER ( 'Table'[Year] ) - 1
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
),
'Table'[FTE]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks, @Jihwan_Kim . When I try this configuration, I am unable to escape the row context of the date, such that I get the current year instead of the previous year.
@charleshale , FTE is a column and you want a new column
new column =
sumx(filter(table, Table[Year] = earlier(Table[Year]) -1 ), [FTE])
If you need a measure
new meausre=
sumx(filter(all(table), Table[Year] = Max(Table[Year]) -1 ), [FTE])
or
new measure=
sumx(filter(allselected(table), Table[Year] = max(Table[Year]) -1 ), [FTE])
The great and ubiquitous @amitchandak ! Thank you for your response. It must be the oddity of my particular model but I get the current year value when I try this.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |