ALLEXCEPT with Year filter

[yamayancha]

Hello,

I am currently working on updating a dashboard I build with data for the last quarter. So far it had included only data for one year (2021) and now that there is another year, one of my calculations stopped working. The data looks like this:

periodID	scenario	value	date
202112	PL	10166298222	20211201
202203	PL	10182400343	20220301
202206	PL	10116872698	20220601
202212	PL	10517026694	20221201

This table is connected to the date table via the date field in a 1 to * relationship (calendar being 1, the table also has data for other scenarios than PL for the same periods).

In my visual I need to always display the value for the last period of the current year. To do this I use the following calculation:

CALCULATE(SUM('table'[value]), 'table'[scenario] = "PL", ALLEXCEPT('Calendar','Calendar'[Year]), 'Calendar'[Month]=12)

The whole page is filtered to period 202203 (March 2022) but when I use my measure in a visual I always get data for both 202112 and 202212 despite the page filter being set to 2022 as evidenced by the Year column:

Shouldn't ALLEXCEPT keep the Year filter intact for the calculation?

[amitchandak]

@yamayancha , try like

CALCULATE(SUM('table'[value]), 'table'[scenario] = "PL", Filter(all('Calendar'), 'Calendar'[Month]=12))

or

CALCULATE(SUM('table'[value]), 'table'[scenario] = "PL", Filter(all('Calendar''[Month]), 'Calendar'[Month]=12))

[yamayancha]

Thank you for your reply.

The first version FILTER(ALL('Calendar')) gives me a similar result to what I had before except that the 2021 value is now also filled outside of the total column:

The second version FILTER(all('Calendar'[Month]) results in an empty table:

[v-yiruan-msft · Accepted solution]

Hi @yamayancha ,

Please update the formula of your measure as below and check whether it can get your desired result...

Measure =
CALCULATE (
    SUM ( 'table'[value] ),
    FILTER (
        'table',
        'table'[scenario] = "PL"
            && YEAR ( 'table'[date] ) = SELECTEDVALUE ( 'Calendar'[Year] )
            && MONTH ( 'table'[date] ) = 12
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards