Calculate on the same sheet with two year filters.

[Syndicate_Admin]

Hello.

I have a table in which I use some measures to realize the total, difference and variation between two years. When using DAX measures the annuities passed them as a fixed value in each of the calculations, I would like to be able to select the year for one column and independently the year for another and perform the calculations.

I put a screenshot and try to explain myself better:

In this table where you put Total19 and Total20, I calculate them as follows:

Total19 - CALCULATE([Total], DATA[ANNUALITY]- 2019)+0

Total20 - CALCULATE([Total], DATA[ANNUALITY]- 2020)+0

I would like those totals to take them depending on the year I took for each of the two columns, and to automatically perform the calculation of variation and difference

Variation - Total20 - Total19

Var - DIVIDE([Variation], [Total19])+0

The idea would be to look like this, to select two years:

All the data I have in the same table, I have an annuity field, I also have in that table a date field (DDMMAAAA).

Greetings and thanks.

[v-yingjl · Accepted solution]

Don't @Syndicate_Admin,

You must extract the year column as a seprate table:

Year table = DISTINCT('Table'[Year])

Create measures like this:

Total1 = SUM('Table'[value])

Total2 =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = SELECTEDVALUE ( 'Year table'[Year] )
            && 'Table'[Category] IN DISTINCT ( 'Table'[Category] )
    )
)

Diff = [Total2] - [Total1]

Attached to a sample file in the following, you hope it could help.

Best regards
Community Support Team _ Yingjie Li
If this post helps,then consider Accepting it as the solution to help other members find it faster.

[v-yingjl · Accepted solution]

Don't @Syndicate_Admin,

You must extract the year column as a seprate table:

Year table = DISTINCT('Table'[Year])

Create measures like this:

Total1 = SUM('Table'[value])

Total2 =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year] = SELECTEDVALUE ( 'Year table'[Year] )
            && 'Table'[Category] IN DISTINCT ( 'Table'[Category] )
    )
)

Diff = [Total2] - [Total1]

Attached to a sample file in the following, you hope it could help.

Best regards
Community Support Team _ Yingjie Li
If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Syndicate_Admin]

Hello, this served me, starting from your example I have a problem, I need you to filter through a city field, total1 does it correctly, but the total2 does not, the city is the same for the two totals, would you have to add another filter in the formula?

[Syndicate_Admin · Accepted solution]

Fixed I created one more line in the filter and it already does what I need.

Greetings.

[Syndicate_Admin]

Hello, thank you very much for the solution, as that is what I need, it is giving me problems the data that shows me, but it is a matter of reviewing the example that you have provided me.

Greetings.

[amitchandak]

@Syndicate_Admin , not very clear. refer this blog does it for two date ranges. Seem like you need for two years

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601