create new column that indicates whether record is first date in slicer

[jasonyeung87]

Hi,

I have a report with a couple of slicers. These filter the records based on the Transaction Month and Transaction Year. These are drop-downs that allow you to select multiple months and Years (or all for both). I want to create a new column in the table that will have either be "Yes" if the transaction is the first month / year of the slicer and "No" otherwise.

For example in the screenshot below, I select All months and 2021 as the year. The first month / year combo will be Jan.2021. As a result, any transactions created from Jan.1 - 31, 2021 will be "Yes", while the others are "No".

Is it possible to create a column that does something like this?

[Anonymous · Accepted solution]

Hi @jasonyeung87 ,

Please try:

Status = 
VAR _min_month =
    CALCULATE ( MIN ( 'Calendar'[MonthNum] ), ALLSELECTED () )
VAR _min_year =
    CALCULATE ( MIN ( 'Calendar'[Year] ), ALLSELECTED () )
VAR _value =
    CALCULATE (
        MIN ( 'Calendar'[Month_Year] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year] = _min_year
                && 'Calendar'[MonthNum] = _min_month
        )
    )
VAR _result =
    IF ( MAX ( 'Calendar'[Month_Year] ) = _value, "Yes", "No" )
RETURN
    _result

The PBIX file is attached for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data