AVERAGE BETWEEN YES AND NO?

[christianfcbmx]

Hi community, good afternoon!!!

I have the following chalange that I cant resolve about bands assistance:

I need to obtain a result out of a column called (Assistance YES OR NO which have blanks too) of my excel table for example: 

If we consider 3 months and 3 bands if they assisted to rehearse 3 consecutive months they get a discount. meaning that if they have a "NO" any month of the three they won't get the discount. (When the column is "BLANK") we dont have to consider that as a "NO" I just don't need to consider blanks in my calculation.

CAN YOU GUYS SUGGEST ME A FORMULA DAX TO SOLVE THAT?

any advice would be so much appreciated!!!: (down below is how my table looks like)

ASSISTANCE TABLE		RESUME 3 LAST MONTHS
MUSIC BANDS	MONTHS	ASSISTANCE YES/NO		MUSIC BANDS	DISCOUNT (EXPECTED RESULT)
BAND1	October	YES		BAND1	YES
BAND2	October			BAND2	YES
BAND3	October	NO		BAND3	NO
BAND1	November	YES
BAND2	November	YES
BAND3	November	YES
BAND1	December	YES
BAND2	December	YES
BAND3	December	YES

[v-yuta-msft · Accepted solution]

@christianfcbmx，

To achieve your requirement, you may create a measure using DAX formlula below then drag MUSIC BANDS and measure to a table visual.

DISCOUNT (EXPECTED RESULT) = 
IF (
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[MUSIC BANDS] ),
            Table1[MONTHS] IN { "December", "November", "October" }
                && (
                    Table1[ASSISTANCE YES/NO] = "YES"
                        || Table1[ASSISTANCE YES/NO] = BLANK ()
                )
        )
    )
        = CALCULATE (
            COUNTROWS ( Table1 ),
            FILTER (
                ALLEXCEPT ( Table1, Table1[MUSIC BANDS] ),
                Table1[MONTHS] IN { "December", "November", "October" }
            )
        ),
    "YES",
    "NO"
)

 

Regards,

Jimmy Tao

[LivioLanzo]

Hello Christian, does it mean that blanks are always considered as 'YES'? What does the real data looks like, does it have all the data and you want to look at just the previous three months?

[christianfcbmx]

Hi, @LivioLanzo!...thats correct, the blanks would be considered always as a YES.

The data contain all data, meaning "more months" and I need to always consider the last 3 months.

Real data looks like this pic (little example)