CODEWITHSUNDEEP
powerbidax
Nick
Nick

Reputation: 3845

How can I show data from the previous working day but exclude weekends?

I have a table visualisation that shows some data. I would like to filter the data so that only data entered the previous working day is shown.

So if the data is refreshed on a Tuesday, then only Monday's data is shown, if on Thursday then only Wednesday data and finally if its refreshed on a Monday, then only Friday's data is shown.

At the moment i can only apply a filter on Power BI desktop for the previous day, which is including weekends (data in the last 1 day)

The data is a simple two column visualisation. In the example below, 17th January is a Friday, so that is the only comment i would like to see on Monday (20th Jan). On the 22nd of January, the only comment i would like to see is the one entered on 21st January:

+-------------+---------------------+
| Date        | Comment             |
+-------------+---------------------+
| 17-Jan-2020 | Defrosting required |
+-------------+---------------------+
| 20-Jan-2020 | Pallet area cleaned |
+-------------+---------------------+
| 21-Jan-2020 | Workshop not locked |
+-------------+---------------------+

What formula can i use for this please?

Upvotes: 0

Views: 5461

Answers (1)

AnkUser
AnkUser

Reputation: 5531

Here is what I did.

Data I have is as below

enter image description here

Then I added a new column IsWeekday which will give me True false for weekday with expression

IsWeekday = If( (WEEKDAY(POC[Date Entered])  <> 1 && WEEKDAY(POC[Date Entered]) <>7); "True";"False")

Now my Entire table looks like below

enter image description here

Now we add filters to Table as below

IsWeekday with only True

enter image description here

Then we add Filter for Field Date Entered as Relative date filtering as is in last days 3 (why 3: because if we are on monday, we want last (sun,sat,friday) does not include today.

enter image description here

Now add one more filter Date Entered

(Note: you are adding Date Entered twice)

enter image description here one for last 3 days and one for Top N (1) and by value with Latest date Entered

Why one more date Entered: Because if we are on 31-Jan-2020 i.e Thursday,

Our first filter will give us Mon,tue,wed,thur,fri

Now our second filter will give us 28-jan,29-jan,30-jan

But we want only 30-jan For (considering 31-jan is today's date) so last filter will come in play and you will have your desired result.

Let me know if this helps.

Upvotes: 2

Related Questions