Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Getting Last Week and Last 7 Days

Hi all,

I have DateTime Column. My requirement is to have slicer values of "Last Week", "Last 7 Days" and "Last month".

Can anyone help on this.

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    You could create a table like

    Date filter = 
VAR StartLastWeek =
	( TODAY( ) - 7 ) - WEEKDAY( ( TODAY( ) - 7 ), 2 ) + 1
VAR EndLastWeek = StartLastWeek + 6
RETURN
	UNION(
		GENERATE(
			{ "Last week" },
			DATESBETWEEN( 'Date'[Date], StartLastWeek, EndLastWeek )
		),
		GENERATE(
			{ "Last 7 days" },
			DATESBETWEEN( 'Date'[Date], TODAY( ) - 7, TODAY( ) - 1 )
		),
		GENERATE(
			{ "Last month" },
			DATESBETWEEN(
				'Date'[Date],
				EOMONTH( TODAY( ), -2 ) + 1,
				EOMONTH( TODAY( ), -1 )
			)
		)
	)

    and create a bi-directional relationship with the date table

1 Reply

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You could create a table like

    Date filter = 
VAR StartLastWeek =
	( TODAY( ) - 7 ) - WEEKDAY( ( TODAY( ) - 7 ), 2 ) + 1
VAR EndLastWeek = StartLastWeek + 6
RETURN
	UNION(
		GENERATE(
			{ "Last week" },
			DATESBETWEEN( 'Date'[Date], StartLastWeek, EndLastWeek )
		),
		GENERATE(
			{ "Last 7 days" },
			DATESBETWEEN( 'Date'[Date], TODAY( ) - 7, TODAY( ) - 1 )
		),
		GENERATE(
			{ "Last month" },
			DATESBETWEEN(
				'Date'[Date],
				EOMONTH( TODAY( ), -2 ) + 1,
				EOMONTH( TODAY( ), -1 )
			)
		)
	)

    and create a bi-directional relationship with the date table