Creating filters for dates - 2 weeks, 1 month, 3 months etc

[HarryT]

Hey there,

I am looking to create some date filters.

I would like to create a new table in my calendar with filters I cvan check off to select a date period.

ending up like this:

Dateview

7 days

14 days

30 days

90 days

180 days

I used a tutuorial to get a Yes / No on the current date and thought I could use something simular to create this date filter

IsToday =

var currentrowdate = FORMAT('Calendar 2009-2025'[Date].[Date], "mm/dd/yyyy")

var istoday = FORMAT(NOW(), "mm/dd/yyyy")

return

IF(istoday = currentrowdate, "Yes", "No")

So what I was thinking was something like (which I know is wrong but you get the idea):

DateView =

'Calendar 2009-2025'[Date].[Date],(today()-7))' is '7 Days'

'Calendar 2009-2025'[Date].[Date],(today()-14))' is '14 Days'

'Calendar 2009-2025'[Date].[Date],(today()-30))' is '30 Days'

'Calendar 2009-2025'[Date].[Date],(today()-90))' is '90 Days'

'Calendar 2009-2025'[Date].[Date],(today()-180))' is '180 Days'

Is this possible?

Thanks so much.  This would really get me over the hump.

[arneh]

Hi,

I'm just thinking out loud...  I added this as a calculated column to my Date Dimension.

VAR __RelativeDay__ =

DATEDIFF(TODAY() , 'Date'[Date], DAY )

RETURN

SWITCH( __RelativeDay__,

-7 , "7 Days",

-14 , "14 Days",

-30 , "30 Days",

-90 , "90 Days",

-180, "180 Days"

)

[HarryT]

Ok thanks for the qucik reply.

I tried a variation of this by adding a column in my table and got a Syntax error:

VAR __RelativeDay__ =

DATEDIFF(TODAY() , 'Calendar 2009-2025'[Date].[Date], DAY )

RETURN

SWITCH( __RelativeDay__,

-7 , "7 Days",

-14 , "14 Days",

-30 , "30 Days",

-90 , "90 Days",

-180, "180 Days"

)

[arneh]

• Do you have a title ?

CalculatedColumn = 

VAR __RelativeDay__ =

DATEDIFF(TODAY() , 'Calendar 2009-2025'[Date].[Date], DAY )

RETURN

SWITCH( __RelativeDay__,

-7 , "7 Days",

-14 , "14 Days",

-30 , "30 Days",

-90 , "90 Days",

-180, "180 Days"

)

[HarryT]

Adding on to my ask,

I want to see data in a range of dates.

Like:

today-7 , "7 Days",

today-14 , "14 Days",

today-30 , "30 Days",

today-90 , "90 Days",

today-180, "180 Days"

[HarryT · Accepted solution]

Ok I ended using this instead:

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

I didn't know it existed and it works great!

I appreciate your assistance before.  

[Anonymous]

Hey,

I created a calcualted column so that I could use this field as a filter for a card visual I had that showed the number within the past 2 weeks. This may or may not work for your situation; it works for mine as the data is always a day out of date and I look at the most recent period as at date data refreshed.

Measure Name =

IF('TABLE'[DATE-FIELD]>=TODAY()-15,1,0)

Replace the above as appropriate (TABLE, DATE-FIELD) with your own table and field

Then filter on the '1' value on your visual using this column 🙂

Hope this helps

[HarryT]

Yes sorry I just figured that part out.

But this just gives me 14 days ago totals and not 0-14.

I need a range.