Forum Discussion
Summarizing on year column using an expression
I have a table AssetStatus containing multiple lines per Asset:
I want to SUM a numeric field in the 1:n related table Assets per Year per Status. I want to do this for the current year and 10 years into the future.
If I have a specific year, I can set a filter:
FILTER(AssetStatus; [StartYear] <= 2021 && ( [EndYear] >= 2021 || ISBLANK(AssetStatus[EndYear]) )
so far so good. I was also able to use a Year table filled with 10 years and adding a SUM column to it:
CALCULATE(SUM(Asset[bvo]);KEEPFILTERS(FILTER(AssetStatus; [Status] = "InBeheer" && [StartYear] <= [Value of Year] && ( [EndYear] >= [Value of Year] || ISBLANK(AssetStatus[EndYear]) ) )))
But how can I group on the AssetStatus[Status] field?
And also, KEEPFILTERS does not use the Slicers I have on Asset fields, do data is very static.
Any help is greatly appreciated!
