Flag to take only the customers that send data for both 2024 and 2025 for the dynamic selection

[Panos_12]

I have the delow table 

and a measure like this 

SO SENT =

VAR CurrentCustomer = SELECTEDVALUE('Sell out'[CustomerCode])

VAR YearsToCheck = {2024, 2025}

-- Get selected month numbers from slicer (e.g., Feb=2, Mar=3, etc.)

VAR SelectedMonths =

    CALCULATETABLE(

        VALUES('Date'[Month]),

        KEEPFILTERS('Date')

    )

-- Exit early if no customer

VAR IsCustomerBlank = ISBLANK(CurrentCustomer)

-- Calculate total combinations to check (e.g., 4 months * 2 years = 😎

VAR MonthCount = COUNTROWS(SelectedMonths)

VAR ExpectedCount = MonthCount * 2

-- Generate all combinations (e.g., Feb-2024, Feb-2025, ..., May-2025)

VAR MonthYearPairs =

    GENERATE(

        SELECTCOLUMNS(YearsToCheck, "Year", [Value]),

        SELECTCOLUMNS(SelectedMonths, "Month", [Month])

    )

-- Loop through combinations and count where customer has Qty > 0 (excluding day 15)

VAR ValidCombinations =

    COUNTROWS(

        FILTER(

            MonthYearPairs,

            VAR ThisYear = [Year]

            VAR ThisMonth = [Month]

            VAR MatchQty =

                CALCULATE(

                    SUM('Sell out'[Qty]),

                    FILTER(

                        ALL('Sell out'),

                        'Sell out'[CustomerCode] = CurrentCustomer &&

                        YEAR('Sell out'[Date]) = ThisYear &&

                        MONTH('Sell out'[Date]) = ThisMonth &&

                        DAY('Sell out'[Date]) = 1 &&

                        'Sell out'[Qty] > 0

                    )

                )

            RETURN

                NOT ISBLANK(MatchQty) && MatchQty > 0

        )

    )

RETURN

    IF(

        IsCustomerBlank,

        "SO Not REC",

        IF(

            ValidCombinations = ExpectedCount,

            "SO REC (Full Month)",

            "SO Not REC"

        )

    )

I did all the steps creating a table like below

SO Status Slicer =

DATATABLE("SO Status", STRING, {

    {"SO Not REC"},

    {"SO REC (Full Month)"}

})

and then fiter ussing the below

SO Status Match =

VAR StatusFromMeasure = [SO SENT]

VAR SelectedStatuses =

    VALUES('SO Status Slicer'[SO Status])  -- supports multi-select too

RETURN

    IF(

        StatusFromMeasure IN SelectedStatuses,

        1,

        0

    )

but there are two issues first of all is not efficient and second when I an adding a new field to the visualisation table the numbers are changing the fields are taken from related table could you please help on that?

[Panos_12]

No I still looking for an answer this issue has not been resolved. I need an answer that will not requires so much resourses because the code I have developed works but needs but it is noe efficient.

[V-yubandi-msft]

Thank you for providing the update. I have attached the updated PBIX file for your review. I hope it aligns with your requirements. When you have a moment, please take a look and let me know if everything appears satisfactory or if there are any changes you would like me to make. Your feedback will help ensure the file meets your expectations.

Regards,
Yugandhar.

[V-yubandi-msft]

Hi @Panos_12 ,
Could you let us know if your issue has been resolved or if you need any further assistance.

Thank you.

[Panos_12]

thank you but still not efficient my logic is more than the below

SO SENT =

VAR SelectedMonths = VALUES('Date'[Month])

VAR YearsToCheck = {2024, 2025}

VAR CurrentCompany = SELECTEDVALUE('Sell out'[CustomerCode])

VAR MonthYearPairs =

    GENERATE(

        SELECTCOLUMNS(YearsToCheck, "Year", [Value]),

        SelectedMonths

    )

VAR ValidCount =

    COUNTROWS(

        FILTER(

            MonthYearPairs,

            VAR ThisMonth = [Month]

            VAR ThisYear = [Year]

            VAR Qty =

                CALCULATE(

                    SUM('Sell out'[Qty]),

                    FILTER(

                        ALL('Sell out'),

                        'Sell out'[CustomerCode] = CurrentCompany &&

                        'Sell out'[MonthColumn] = ThisMonth &&

                        'Sell out'[YearColumn] = ThisYear &&

                        Day('Sell out'[Date]) <> 15 &&

                        NOT(ISBLANK('Sell out'[Qty]))

                    )

                )

            RETURN Qty > 0

        )

    )

VAR ExpectedCount = COUNTROWS(SelectedMonths) * 2

RETURN

    IF(ValidCount = ExpectedCount, 1, 0) but in terms of resources is very demanding for large tables also the Date and the Sell Out tables are related. Finally, I would like this measure to be converted to a slicer to be added to the report. I think that is a very complicated issue

[V-yubandi-msft]

Hi @Panos_12 ,
Thanks for sharing. I agree with your approach. While your current logic works, the GENERATE + ALL scan can be resource intensive. You might consider using a YearMonth key to compare counts, or pre-flagging completeness in Power Query. This would make your measure lighter and more efficient.

Regards
Yugandhar.

[V-yubandi-msft]

Hi @Panos_12 ,

Thank you for clearly outlining the scenario, it helped me understand your requirements.

You want to display only companies with complete sales data for both 2024 and 2025 across all selected months, such as January, February, and March, and ensure the results remain consistent even when fields like Product are added to the visual.

I’ve attached the Power BI file for your reference. Please let me know if you need any changes or improvements.

Best regards,

Yugandhar.

[johnt75]

If you have a date table linked to 'Sell Out'[Date] and the slicers for year and month are both coming from the Date table, you can create a measure like

SO Sent =
IF (
    HASONEVALUE ( 'Sell out'[CustomerCode] ),
    VAR DatesAndValues =
        ADDCOLUMNS (
            VALUES ( 'Date'[Year month] ),
            "@sales", CALCULATE ( SUM ( 'Sell out'[Qty] ) )
        )
    VAR NumMonths =
        COUNTROWS ( DatesAndValues )
    VAR NumMonthsWithSales =
        COUNTROWS ( FILTER ( DatesAndValues, [@sales] > 0 ) )
    VAR Result =
        IF ( NumMonths = NumMonthsWithSales, "SO REC (Full Month)", "SO Not REC" )
    RETURN
        Result
)

[Panos_12]

Thank you but the above is not filtering correctly the companies

[Panos_12]

and then for these companies I would like to add to the visualization table a field like the products that they are selling and the number be correct and do not change.

[Panos_12]

Hi here is an example below 

So suppose that I have data for the 3 month above for Jan, Feb and March and for the company C I do not have data for January 2024, for company A I do not have data for February 2024 and for the company E I do not have data for March 2025, so when the user selected these 3 months and for the filter the selection SALES RECEIVED I would like to to be appeared only the companies B and D and their cumulative data. So the companies that I have data for both 2024 and 2025 for the month selection. Thank you

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Panos_12]

Sample Data
Month | Company | Sales QTY 2024 | Sales QTY 2025
-----------------------------------------------------
January | Company A | 2 | 3
January | Company B | 23 | 12
January | Company C | (missing) | 11
January | Company D | 13 | 1
January | Company E | 2 | 17

February | Company A | (missing) | 22
February | Company B | 231 | 112
February | Company C | 4 | 11
February | Company D | 13 | 1
February | Company E | 2 | 7

March | Company A | 2 | 3
March | Company B | 23 | 12
March | Company C | 12 | 11
March | Company D | 13 | 1
March | Company E | 2 | (missing)

Explanation

Company C is missing data for January 2024.

Company A is missing data for February 2024.

Company E is missing data for March 2025.

So, when filtering Jan + Feb + Mar for SALES RECEIVED, I only want to see companies that have data in both years (2024 & 2025) for all selected months.

Expected Result (Cumulative)
Company | Total Sales 2024 | Total Sales 2025
------------------------------------------------
Company B | 277 | 136
Company D | 39 | 3