Forum Discussion
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...
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)))
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)))