CODEWITHSUNDEEP
powerbi
Sona123
Sona123

Reputation: 91

Power BI filter out data based on previous business day

I have a column ReceiptDateTime which has the Date and time of receipt received. I would like to filter and use only the data which is there for the previous business day (Monday to Friday). For eg on Monday I would like to see only Friday's data. I am trying to achieve this in power BI.

Is this possible? Really appreciate your help.

Upvotes: 1

Views: 2907

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5192

If I start with this table loaded into Power BI as Table1:

enter image description here

I then add a new column (Modeling -> New Column) with this code...ReceiptDateOnly = DATE(YEAR([ReceiptDateTime]),MONTH([ReceiptDateTime]),DAY([ReceiptDateTime]))...to get only the date from the ReceiptDateTime column. (You had indicated you had date/time combinations, and we'll need just the dates for what I'll do here.)

enter image description here

I then create a new table (Modeling -> New Table) with this code...`Table2 = GROUPBY(Table1,Table1[ReceiptDateOnly],"SomeData",sumx(CURRENTGROUP(),Table1[SomeData])).

Then I add a new column with this code...

PreviousWorkday = if(WEEKDAY('Table2'[Table1_ReceiptDateOnly])=2, DATEADD('Table2'[Table1_ReceiptDateOnly],-3,DAY), if(WEEKDAY('Table2'[Table1_ReceiptDateOnly])=1, DATEADD('Table2'[Table1_ReceiptDateOnly],-2,DAY), DATEADD('Table2'[Table1_ReceiptDateOnly],-1,DAY)) )

...to determine the previous workday.

Then I add another column with this code...

PreviousWorkDaySomeData = SUMX(FILTER('Table2','Table2'[Table1_ReceiptDateOnly]=EARLIER('Table2'[PreviousWorkday])),'Table2'[SomeData])

...to get the data related to the previous workday.

I get a table like this:

enter image description here

Monday uses Friday's data. Tuesday uses Monday's data. Wednesday uses Tuesday's data. Thursday uses Wednesday's data. Friday uses Thursday's data. Saturday uses Friday's data. Sunday uses Friday's data.

Upvotes: 1

Related Questions