Combine Measure to filter by conditions

[Padagon]

Hi everybody, hope you're doing great

I need some advise to solve an issue.

I have the next information (a sample), this is a table that keeps growing over time

Month	Year	Name	Segment	Region	Income	Cost	Expenses	Forecast
1	2022	AAA	Seg1	North	100	10	101	50
2	2022	BBB	Seg2	Center	200	20	201	50
3	2022	CCC	Seg1	South	300	30	301	50
1	2023	AAA	Seg1	North	400	40	401	50
2	2023	BBB	Seg2	Center	500	50	501	50
3	2023	CCC	Seg1	South	600	60	601	50
1	2024	AAA	Seg1	North	700	70	701	50
2	2024	BBB	Seg2	Center	800	89	891	50
3	2024	CCC	Seg1	South	900	90	901	50

Of course, for this sample numbers are random (and nonsense here)

As you can see, I have  certain names that belong to Segment 1, others to Segment 2, and so on.., but that doesn't mean that Seg1 belongs to same Region, although I could have more names that belong to Seg1 and North region

What I would like to do, is to create a Calculated Measure, actually many calculated measures, that calculates the change income between Years, but I also need to add the condition to select those years.

What do I mean?

Suppose I name the calculated measure Income Change. The calculated measure should calculate the Income between a Year I select by using a filter, and give the income with the previous year so, if in a filter (or selection) I choose Y2023 it should select Y2022 automatically to do  Income Change = (Income in Y2023 - Income in Y2022)/ Income in Y2022 . Then if I choose on the filter Y2024 then it should do Income Change = (Income in Y2024 - Income in Y2023)/ Income in Y2023 . Or select those 2 years and apply them to the Calculated Measure.  I know that using some filters for Name, Region, Segment the calculated measure will do the magic to do the desired results when considering them. But what I need to do is to choose some how the 2 values (Year and PreviousYear) to automatically do the change in the calculated measure.  Also, I need to consider the month, because I need to compare the Income for the same month in 2 different years. 

The same thing I'll need to do with change in Expenses and change in Forecast, but once I have one I know I could do the rest.

Thanks.

[v-junyant-msft]

Hi @Padagon ,

Since you need to select two years (this year and previous year), in order to eliminate the mutual filtering effect, I suggest you add two new tables as slicers without any relationships:

Then use this DAX to create a measure:

Income Change = 
VAR _Year = SELECTEDVALUE(SlicerYear[Year])
VAR _PreviousYear = SELECTEDVALUE(SlicerPreviousYear[PreviousYear])
VAR _Income_Year = 
CALCULATE(
    SUM('Table'[Income]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = _Year && 'Table'[Month] IN VALUES('Table'[Month]) && 'Table'[Name] IN VALUES('Table'[Name]) && 'Table'[Segment] IN VALUES('Table'[Segment]) && 'Table'[Segment] IN VALUES('Table'[Segment])
    )
)
VAR _Income_PreviousYear = 
CALCULATE(
    SUM('Table'[Income]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = _PreviousYear && 'Table'[Month] IN VALUES('Table'[Month]) && 'Table'[Name] IN VALUES('Table'[Name]) && 'Table'[Segment] IN VALUES('Table'[Segment]) && 'Table'[Segment] IN VALUES('Table'[Segment])
    )
)
RETURN
_Income_Year - _Income_PreviousYear

And the final output is as below:

In my case, the Year and Previous Year slicers were created using the newly created table, and the other slicers used columns from the original table.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Padagon]

I can see what you did, but it not what I want, as I already tried it. I don't want to use 2 filters on de dashboard, that wouldn't be practical. I would like to select only one year, and get the calculations of the previous year automatically by using that measure.

I am not sure if this is possible.

Thanks for your answer though.

[v-junyant-msft · Accepted solution]

Hi @Padagon ,

Oh sorry, I thought you were talking about the previous year just as an example, and assumed that any earlier years would be covered as well😂.
Just change the DAX into this:

Income Change = 
VAR _Year = SELECTEDVALUE(SlicerYear[Year])
VAR _PreviousYear = _Year - 1
VAR _Income_Year = 
CALCULATE(
    SUM('Table'[Income]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = _Year && 'Table'[Month] IN VALUES('Table'[Month]) && 'Table'[Name] IN VALUES('Table'[Name]) && 'Table'[Segment] IN VALUES('Table'[Segment]) && 'Table'[Segment] IN VALUES('Table'[Segment])
    )
)
VAR _Income_PreviousYear = 
CALCULATE(
    SUM('Table'[Income]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = _PreviousYear && 'Table'[Month] IN VALUES('Table'[Month]) && 'Table'[Name] IN VALUES('Table'[Name]) && 'Table'[Segment] IN VALUES('Table'[Segment]) && 'Table'[Segment] IN VALUES('Table'[Segment])
    )
)
RETURN
_Income_Year - _Income_PreviousYear

Just change here:

VAR _PreviousYear = _Year - 1

And the final output is as below:

(900+800+700) - (600+500+400) = 900

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.