WeekEnding date slicer

[HKInd]

I have below data set:-

Table Name:- Order data

Country	City	Date	Order	Rate	Total Cost
Country1	City1	7/1/2023	18	216	3888
Country2	City2	7/2/2023	27	191	5157
Country3	City3	7/3/2023	37	375	13875
Country4	City4	7/4/2023	13	47	611
Country5	City5	7/5/2023	40	215	8600
Country6	City6	7/6/2023	57	133	7581
Country7	City7	7/7/2023	24	245	5880
Country8	City8	7/8/2023	30	152	4560
Country9	City9	7/9/2023	51	75	3825
Country10	City10	7/10/2023	28	168	4704
Country11	City11	7/11/2023	39	59	2301
Country12	City12	7/12/2023	47	78	3666
Country13	City13	7/13/2023	28	303	8484
Country14	City14	7/14/2023	29	259	7511
Country15	City15	7/15/2023	37	117	4329
Country1	City1	7/1/2023	59	319	18821
Country2	City2	7/2/2023	70	371	25970
Country3	City3	7/3/2023	67	42	2814
Country4	City4	7/4/2023	50	377	18850
Country5	City5	7/5/2023	15	389	5835
Country6	City6	7/6/2023	53	211	11183
Country7	City7	7/7/2023	21	235	4935
Country8	City8	7/8/2023	53	279	14787
Country9	City9	7/9/2023	49	59	2891
Country10	City10	7/10/2023	56	390	21840
Country11	City11	7/11/2023	56	105	5880
Country12	City12	7/12/2023	45	159	7155
Country13	City13	7/13/2023	60	395	23700
Country14	City14	7/14/2023	42	219	9198
Country15	City15	7/15/2023	33	387	12771

I have another calculated table: Note:- Weekending is Sunday

"(Weekending = 'Date Calendar'[Date]-WEEKDAY('Date Calendar'[Date],2)+7)"

In canvas, I have a slicer with fields - Weekending

So i am able to create below metrix visual:

Selected_date = SELECTEDVALUE('Date Calendar'[Weekending])

Monday_cost =

VAR Monday_date = [Selected_date]-6

RETURN

CALCULATE(SUM('Order data'[Total Cost]),FILTER('Order data','Order data'[Date]=Monday_date))

Tueday_cost =

VAR tuesday_date = [Selected_date]-5

RETURN

CALCULATE(SUM('Order data'[Total Cost]),FILTER('Order data','Order data'[Date]=tuesday_date))

So I am asking is there any other effiecient way to do this and if i want to select multiple weekending from slicer it does not work.

[Nithinr]

if you want it filter it for all weekdays do it at dataset level. this was it will reduce dataset overhead too.

If this helps you, please mark my solution as accepted so that others can find it quickly while facing a similar issue. Thank you!

[HKIndia]

@Nithinr  Thanks for your reply!

Can we add an entry for Current week and should be set as default. so that when user open the report, should get current week selected.

[Nithinr]

@HKIndia Solved: Select always the current week within Slicer - Microsoft Fabric Community try this, Also I see you have marked wrong comment as solution for this topic. Please check.

[Nithinr]

Create Another column weekday in calendar table 

weekday = WEEKDAY('Date Calendar'[Date],2)  , Create a relationship between order data and calendar table with date column.     Tweak the measures to use weekday column . you can select multiple weekend dates and get sum of those weekdays you want. weekday= 1-monday,2-tuesday,3-wednesday .....

Monday_cost = 

CALCULATE(SUM('Table'[Total Cost]),FILTER('Date Calendar','Date Calendar'[weekday]=1))

          

[HKInd · Accepted solution]

@Nithinr Thanks for your quick help on this:-

I have to apply one more condition like : order >50

Monday_cost =

CALCULATE(SUM('Order data'[Total Cost]),FILTER('Order data','Order data'[Weekday]=2),FILTER('Order data','Order data'[Order]<50))

It is providing correct answer but any other better way