Forum Discussion

obriaincian's avatar
obriaincian
Resolver I
4 years ago
Solved

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...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
obriaincian
Resolver I
4 years ago

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