Calculated column which gives dynamic results

[ishwariya11195]

I have the follwoing table which a fact table but I have reduced the number of columns here. 

The goal is to have the calculated columne "Calculated country name" which does the follwoing:

If the number of manufacturers are less than 3 for a selected year in the filter, the column should evlaute the consition for selected year and selected year -1 ,if number of manufactres is less than 3, if yes , "Others", else "Country name".

for example, if 2023 is slected, it evaulated the coniditon for 2023 and 2022 , gives the results.

2024 is sleeected, it evaluated for 2024 and 2023.

the below formula is working if hardcoded but not when applied selected filters. kindly help

PBI Calc Country Name =

VAR MinTotalSellers =

    MINX(

        FILTER(

            'PBI Fact Others',

            'PBI Fact Others'[Country] = EARLIER('PBI Fact Others'[Country]) &&

                    'PBI Fact Others'[Product Category] = EARLIER('PBI Fact Others'[Product Category]) &&

            ('PBI Fact Others'[Year] = 2022 || 'PBI Fact Others'[Year] = 2023)

        ),

        'PBI Fact Others'[Total Sellers]

    )

RETURN

    IF(MinTotalSellers < 3, "Others", 'PBI Fact Others'[Country])

Country	Product name	No.of.Manufacturers		Calculated country name
India	Trolley	1	2022	Others
Malasyia	Frame	2	2022	Others
India	Trolley	4	2023	Others
Malasyia	Frame	4	2023	Others
India	Trolley	5	2024	India
Malasyia	Frame	6	2024	Malasyia

[rajendraongole1 · Accepted solution]

Hi @ishwariya11195  - can you please check the below measure based on selected filter, hope it works.

PBI Calc Country Name =

VAR SelectedYear = MAX('Mfyear'[Year])

VAR Previou = SelectedYear - 1

VAR ManufacturersCurrentYear =

    CALCULATE(

        DISTINCTCOUNT('Mfyear'[No.of.Manufacturers]),

        'Mfyear'[Year] = SelectedYear

    )

VAR ManufacturersPreviousYear =

    CALCULATE(

        DISTINCTCOUNT('Mfyear'[No.of.Manufacturers]),

        'Mfyear'[Year] = Previou

    )

VAR ManufacturersTotal = ManufacturersCurrentYear + ManufacturersPreviousYear

RETURN

    IF(ManufacturersTotal < 3, "Others", MAX('Mfyear'[Country]))

I hope this works.