Forum Discussion

HKInd's avatar
HKInd
Helper I
3 years ago
Solved

WeekEnding date slicer

I have below data set:-

Table Name:- Order data

CountryCityDateOrderRateTotal Cost
Country1City17/1/2023182163888
Country2City27/2/2023271915157
Country3City37/3/20233737513875
Country4City47/4/20231347611
Country5City57/5/2023402158600
Country6City67/6/2023571337581
Country7City77/7/2023242455880
Country8City87/8/2023301524560
Country9City97/9/202351753825
Country10City107/10/2023281684704
Country11City117/11/202339592301
Country12City127/12/202347783666
Country13City137/13/2023283038484
Country14City147/14/2023292597511
Country15City157/15/2023371174329
Country1City17/1/20235931918821
Country2City27/2/20237037125970
Country3City37/3/202367422814
Country4City47/4/20235037718850
Country5City57/5/2023153895835
Country6City67/6/20235321111183
Country7City77/7/2023212354935
Country8City87/8/20235327914787
Country9City97/9/202349592891
Country10City107/10/20235639021840
Country11City117/11/2023561055880
Country12City127/12/2023451597155
Country13City137/13/20236039523700
Country14City147/14/2023422199198
Country15City157/15/20233338712771

 

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.

 

Need Help
  • HKInd's avatar
    HKInd
    3 years ago

    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

5 Replies

  • Nithinr's avatar
    Nithinr
    Resolver III
    3 years ago

    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's avatar
      HKInd
      Helper I
      3 years ago

      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
  • Nithinr's avatar
    Nithinr
    Resolver III
    3 years ago

    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!