Filter Last 1 or 2 Days based weekday

[Anand24]

Hello Experts,

I have a scenario wherein i want to filter the data on date column based on current day.

Scenario:

If refreshing on Monday, show data in table visualization for last 2 days(Sunday and Saturday) of date column.

If refreshing on any other day(Tuesday to Sunday), load data for last 1 day of date column.

Input Data

Record_Date	Category
21-08-2020	a
21-08-2020	b
21-08-2020	c
22-08-2020	d
22-08-2020	e
22-08-2020	f
23-08-2020	g
23-08-2020	h
23-08-2020	j
24-08-2020	i
24-08-2020	j
24-08-2020	k
25-08-2020	l
25-08-2020	m
25-08-2020	n

Expected Output:

1. If report refreshes on 24th August(Monday), show data in table visualization for last 2 days, 23rd August(Sunday) and 22nd August(Saturday)

Record_Date	Category
22-08-2020	d
22-08-2020	e
22-08-2020	f
23-08-2020	g
23-08-2020	h
23-08-2020	j

2. If report refreshes on any other day, say 25th August(Tuesday), show data in table visualization for last 1 day, 24th August(Monday)

Record_Date	Category
24-08-2020	i
24-08-2020	j
24-08-2020	k

Note:

1. Data is fully loaded, i.e., for all dates and not just for last 1 or 2 days.

2. New column or measure using DAX is preferable instead of Power Query changes

Thanks is advance !!! 🙂

[v-kelly-msft]

Hi @Anand24 ,

First create a table as below:

Table 2 = VALUES('Table'[Record_Date])

Then create a measure as below:

_Fiterdate = 
var _weekday=CALCULATE(MAX('Table'[Weekday]),FILTER(ALL('Table'),'Table'[Record_Date]=SELECTEDVALUE('Table 2'[Record_Date])))
var _date1=CALCULATETABLE(VALUES('Table'[Record_Date]),FILTER(ALL('Table'),'Table'[Record_Date]=SELECTEDVALUE('Table 2'[Record_Date])-1||'Table'[Record_Date]=SELECTEDVALUE('Table 2'[Record_Date])-2))
var _date2=CALCULATETABLE(VALUES('Table'[Record_Date]),FILTER(ALL('Table'),'Table'[Record_Date]=SELECTEDVALUE('Table 2'[Record_Date])-1))
Return
IF(_weekday=1 && MAX('Table'[Record_Date]) in _date1,MAX('Table'[Record_Date]),IF(_weekday<>1&&MAX('Table'[Record_Date]) in _date2,MAX('Table'[Record_Date]),BLANK()))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mahoneypat · Accepted solution]

Please try this measure expression

Result =
VAR thisday =
    TODAY ()
VAR days =
    IF ( WEEKDAY ( thisday ) = 2, 2, 1 )
RETURN
    CALCULATE (
        [Your Measure],
        FILTER (
            ALL ( Table[Record_Date] ),
            Table[Record_Date] >= thisday - days
                && Table[Record_Date] < thisday
        )
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anand24]

Awesome !!!

Thank You @mahoneypat !!! That worked in one go !!!

[SteveCampbell]

You can use a DAX date table, such as this one from https://powerbi.tips/2017/11/creating-a-dax-calendar/

Dates 5 =
  GENERATE (
    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday    
    VAR currentDay = [Date]
    VAR days = DAY( currentDay )
    VAR months = MONTH ( currentDay )
    VAR years = YEAR ( currentDay )
    VAR nowYear = YEAR( TODAY() )
    VAR nowMonth = MONTH( TODAY() )
    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
    VAR todayNum = WEEKDAY( TODAY() )
    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
  RETURN ROW (
    "day", days,
    "month", months,
    "year", years,
    "day index", dayIndex,
    "week index", weekIndex,
    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
    "year index", INT( years - nowYear )
  )
)

Then you can filter on the day index for -1 and -2

[Anand24]

Hi @SteveCampbell ,

Thank you for the response !

I'm afraid this won't work in my case. This table is leaving values for weekends whereas i still need them in my case. Also, I'm not sure how will I conditionally filter my data on weekday using this.

My scenario is pretty straight forward:

IF today's Weekday = Monday, THEN Return data for last 2 days(saturday and friday)

Else Return data for last 1 day