Power BI - Filtering Dates

[obriaincian]

I have a Power BI related question. I am currently using the below measure to filter dates. What I am attempting to do is filter on any dates from the previous month up to todays date. (Note that the formula needs to by dynamic enough so that the year change won't affect it. e.g. we are now in 2022 but I am capturing data from Dec 2019 up until future dates).

todaysDate = TODAY()

FilterOnPreviousMonth = 
var filteredResult = IF(DATEDIFF(MAX(Tasks[Target Date]),[todaysDate],MONTH)=1,1,0)
return filteredResult

Any help would be greatly appreciated.

Many thanks

[smpa01]

@obriaincian  you can use this filter expression goes one period back (YRMO) than a current period and brings everything from that period till today.

EVALUATE
VAR _x =
    CONVERT (
        MINX (
            TOPN (
                2,
                SUMMARIZE (
                    ADDCOLUMNS (
                        VALUES ( Actuals[activity_date] ),
                        "YRMO",
                            YEAR ( Actuals[activity_date] )
                                &
                                VAR _mo =
                                    MONTH ( Actuals[activity_date] )
                                VAR _len =
                                    LEN ( _mo )
                                RETURN
                                    IF ( _len = 1, "0" & _mo, _mo )
                    ),
                    [YRMO]
                ),
                [YRMO], DESC
            ),
            [YRMO]
        ),
        INTEGER
    )
RETURN
    FILTER (
        Actuals,
        CONVERT (
            YEAR ( Actuals[activity_date] )
                &
                VAR _mo =
                    MONTH ( Actuals[activity_date] )
                VAR _len =
                    LEN ( _mo )
                RETURN
                    IF ( _len = 1, "0" & _mo, _mo ),
            INTEGER
        ) >= _x
    )

It would be much easier if you have a YearMonth column,e,g, 202001,202002....202201

EVALUATE
FILTER (
    Actuals,
    Actuals[fiscal_period]
        = MINX (
            TOPN (
                2,
                VALUES ( Actuals[fiscal_period] ),
                Actuals[fiscal_period], DESC
            ),
            [fiscal_period]
        )
)

[obriaincian]

@smpa01 thank you for your answer. To add to the above how would I go about filtering dates to include all dates from the previous month forward. (i.e. any dates from last month up until today and also any future dates)? 

Thanks

[smpa01 · Accepted solution]

@obriaincian  I misread the Q. There is an easier filter expression like the following

Table = FILTER(Actuals,[activity_date]>=EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-1))

Measure = calculate(<aggregation>,FILTER(Actuals,[activity_date]>=EDATE(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-1)))

[amitchandak]

@obriaincian , Based on today ,refer following measures

TIll date from last month =
var _min = eomonth(today(),-2)+1
var _max = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

MTD=
var _min = eomonth(today(),-1)+1
var _max = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

LMTD =
var _min = eomonth(today(),-2)+1
var _max1 = today()
var _max = date(year(_max1),month(_max1)-1, day(_max1))
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

YTD Today=
var _min = date(year(today()),1,1)
var _day = today()
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _day) )

[obriaincian]

@amitchandak I'm not trying to calculate anything, I would just like to filter on a date column to only include dates from last month (i.e Dec 2021) and any dates after Dec 2021. Thanks