Sum with filter

[IF]

Hi,

I would like to write a measure that would be 13-4-24=-15.

It should be calculated based on (13 is the total value for redeveloped&high) - (4 is the total value for redeveloped&high for the previous year) - (24 is the total value for financial&high) 

Date	Country	Type	Volume	Total
Nov-22	DE	Redeveloped	Low	12
Nov-22	DE	Redeveloped	High	13
Nov-22	DE	Financial	Low	21
Nov-22	DE	Financial	High	24
Nov-22	DE	Source	Low	34
Nov-22	DE	Source	High	35
Nov-21	DE	Redeveloped	Low	3
Nov-21	DE	Redeveloped	High	4
Nov-21	DE	Financial	Low	5
Nov-21	DE	Financial	High	2
Nov-21	DE	Source	Low	6
Nov-21	DE	Source	High	9

Can you help me to write a measure for this? I want to use it for a chart to show the periods. I have 2 millions of records.

Thanks

[TomasAndersson]

Hi!
This year's redeveloped and financial should be a simple CALCULATE(). Last years value requires you to indicate what last year. If you have a date table, SAMEPERIODLASTYEAR() should work, but otherwise you need to find a different solution (e.g. store this year's year as a variable and then use ALL() and filter out this year's year -  1). 

Something like this might help you going forward.

Measure :=
VAR __ThisYearRedeveloped = 
CALCULATE(
    SUM(Table[Total]),
    Table[Type] = "Redeveloped",
    Table[Volume] = "High"
)
VAR __LastYearRedeveloped = 
CALCULATE(
    _ThisYearRedeveloped,
    SAMEPERIODLASTYEAR(Dates[Date]) //Adjust according to your table names
)
VAR __ThisYearFinancial = 
CALCULATE(
    SUM(Table[Total]),
    Table[Type] = "Financial",
    Table[Volume] = "High"
)
return
__ThisYearRedeveloped - __LastYearRedeveloped -__ThisYearFinancial

Hope this helps!

[IF]

Hi Thanks for the help, but it is not working. When I tried the measure, it is not exactly counting as I explained. If i don't select the date, it counts for the whole records. For example; 

VAR __ThisYearRedeveloped =

CALCULATE(

    SUM('Table'[Total]),

    'Table'[Type] = "Redeveloped",

    'Table'[Volume] = "High"

)

gives the result: 17

Similarly;

ThisYearFinancial =

CALCULATE(

    SUM('Table'[Total]),

    'Table'[Type] = "Financial",

    'Table'[Volume] = "High"

)

gives the result: 26

[TomasAndersson · Accepted solution]

Yes, you somehow have to indicate what year "this year" is. If you don't do that through a date slicer or filter the visual or report page itself it needs to be done in another way.

It shouldn't be more complicated than to create a new variable that tells the next what "this year" is, and then use it as an additional filter condition later on: 

VAR __ThisYear = 
-- some type of measure

VAR __ThisYearRedeveloped = 
CALCULATE(
    SUM(Table[Total]),
    Table[Type] = "Redeveloped",
    Table[Volume] = "High",
    YEAR(Table[Date]) __ThisYear
)

So the question is how you define "this year". Is it TODAY(), i.e. 2023 now? Is it MAX(Table[Date]), i.e. 2022 if that is the latest data you have? Is it something else? That logic needs to be added.