Problem: I have data for all weekdays, but I do not want to show data on weekends, I would like to show the same on Monday
Solution:
There is a solution like creating a new date column and moving weekend to Monday or Friday and join the new column with the date.
But we would like to have a solution using only measures. This means we need to move weekend data by 1–2 days on either side
Columns I already have in the Model
Discount = [Gross Sales]*[Dis Per]/100.0
Gross Sales = [Qty] * [Price]
Net Sales = [Gross Sales] - [Discount]
Measure I am using
Net = Sum(Sales[Net Sales])
New measures created for weekend data moved to Monday
Net non Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) >=6 ))
Net non work 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],-2,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],-1,DAY))
Net Work = CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) <6 ))
Net Show on Monday = [Net Work] + [Net non work 1]
New measures created for Friday
Net non work b 1 = CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =6 )) , dateadd('Date'[Date],1,DAY)) + CALCULATE(CALCULATE([net] , filter('Date', WEEKDAY('Date'[Date],2) =7 )) , dateadd('Date'[Date],2,DAY))
Net Show on Friday = [Net Work] + [Net non work b 1]
Find more details on the Blog
