Show workdays only and add all weekend and holidays data to the next workday

[rschaudhr]

I have the following senario. There are values for workday, weekend and holidays:

Holiday Name	Day of the week	Date	Amount
	Saturday	26-Jun-21	50
	Sunday	27-Jun-21	50
	Monday	28-Jun-21	200
	Tuesday	29-Jun-21	100
	Wednesday	30-Jun-21	50
Canada Day	Thursday	01-Jul-21	150
	Friday	02-Jul-21	300
	Saturday	03-Jul-21	200
	Sunday	04-Jul-21	100
	Monday	05-Jul-21	450
	Tuesday	06-Jul-21	400
	Wednesday	07-Jul-21	50

What I was to do is show only workdays (remove any weekdays and holidays). However, add the values from weekend and holidays to the next workday. So, the above file should look like the following:

Holiday Name	Day of the week	Date	Amount	New Amount
	Monday	28-Jun-21	200	300
	Tuesday	29-Jun-21	100	100
	Wednesday	30-Jun-21	50	50
	Friday	02-Jul-21	300	450
	Monday	05-Jul-21	450	750
	Tuesday	06-Jul-21	400	400
	Wednesday	07-Jul-21	50	50

Is there a way to do this?

[ryan_mayu · Accepted solution]

@rschaudhr 

here is a workaround for you

Column = 
VAR last=maxx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])-1),'Table'[Amount])
VAR last2=maxx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])-2),'Table'[Amount])
VAR holiday=maxx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])-1),'Table'[Holiday Name])
return if('Table'[Holiday Name]<>""||'Table'[Day of the week]="Saturday"||'Table'[Day of the week]="Sunday",blank(),if('Table'[Day of the week]="Monday",'Table'[Amount]+last+last2,if(holiday<>"",'Table'[Amount]+last,'Table'[Amount])))

please see the attachment below

[ryan_mayu · Accepted solution]

@rschaudhr 

here is a workaround for you

Column = 
VAR last=maxx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])-1),'Table'[Amount])
VAR last2=maxx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])-2),'Table'[Amount])
VAR holiday=maxx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])-1),'Table'[Holiday Name])
return if('Table'[Holiday Name]<>""||'Table'[Day of the week]="Saturday"||'Table'[Day of the week]="Sunday",blank(),if('Table'[Day of the week]="Monday",'Table'[Amount]+last+last2,if(holiday<>"",'Table'[Amount]+last,'Table'[Amount])))

please see the attachment below

[rschaudhr · Accepted solution]

Thank you for the solution. It works well. 

[amitchandak]

@rschaudhr , You can create these columns in your date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]>EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])

User Work Date Cont  in your analysis