Forum Discussion
Power BI - Filtering Dates
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
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)))
5 Replies
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) )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
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] ) )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
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)))
