Trying to create a conditional filter using dates

[Anonymous]

I'm trying to create a visual that will show data from -2 buisness days ago. The idea is that if its Monday I need to have the data from Thursday and on Tuesday I need the data from Friday. This is posing to be a problem for these two days as filtering for -2 days ago shows Sat and Sun, both of which have no data. Any ideas on how to make this possible?

[rsbin]

@Anonymous ,

You should be using a Date Table in your model.  Then add a 1-many relationship from this table to your Fact Table.

Lots of info in here or on the web for how to create this table.

This date table should include a Day of Week Column.  If you want you can then create another Column called something like IsWeekday?

Add one of these columns to your Filter Pane for this Visual and deselect the weekend days.

If you don't want to add a proper Date table, then simply create a calculated column using the WEEKDAY function.  Then use this column to filter out the weekend days.

Hope this gets you going in the right direction.

Regards,

[Anonymous]

Thanks for your response @rsbin. I've already incorperated a date table as well as a column using the weekday function. As of now the data that the visual is based upon is governed in access with the if statement "IIf(Weekday(Date())=2,Date()-4,IIf(Weekday(Date())=3,Date()-4,IIf(Weekday(Date())=4,Date()-2,IIf(Weekday(Date())=5,Date()-2,IIf(Weekday(Date())=6,Date()-2,Date()-2)))))". I have a column that recreates this function but I can only filter to a specific day ex. 10/31/2022 as apposed to -2 work days ago. If I use the relative date filter (as of wrinting) it inculdes data from 10/31/2022 and 11/1/2022; I'm wanting to get data from 10/24 on 10/31 and 10/25 on 11/1  and so on. I've found no such way to do this yet. Any Ideas?

[rsbin]

@Anonymous ,

Based on the IIF statement in Access, looks to me like that is already ignoring Weekends  Can you post a small sample of your data table? Please post as a table, not an image and be sure to exclude any sensitive or confidential data.

[Anonymous]

Order#		Date		Qty
4524		1/2/20		28
4525		1/2/20		27
4526		1/2/20		43
4527		1/3/20		53
4528		1/3/20		46
4529		1/6/20		67
4530		1/6/20		87
4531		1/7/20		51
4532		1/8/20		85
4533		1/8/20		64

 This is a boiled down table I created to mimic the data I have; this shows data from a Thursday to a Wednsday. I apologize for not explaining before but I am trying to eliminate the need for the access filter in favor of filtering in Power Bi. This would simplify the proccess of having to go back to review a report from a month ago or a week ago. Again thank you for the help @rsbin. 

[rsbin]

@Anonymous ,

So, I'm going to assume you want to do something like SUM Qty by Day.

Join Date from this table to the Date in your DateTable.

Then in whatever visual you choose, you can just bring in the Date Column from the Date Table, and bring in Qty and do your SUM.

Or create a simple measure:

QTY_SUM = SUM(YourTable[Qty])

The fact that your fact table does not have data for Sat and Sun., appears to me the problem is taken care of.

If you only need to show data for 2 days ago, you can use the Relative Date Filter on Date, but also add the Day of Week to the Filter pane and filter out weekends.

The following picture demonstrates this.  IsWeekday = TRUE, and I am showing the past two days excluding today.  This will work come next Monday, it will show Thurs and Fri.

I hope this demonstrates how you can accomplish what you want.

Signing off now, but we can pick up on this tomorrow morning, if necessary.

Regards,