Forum Discussion

nardtmo's avatar
nardtmo
Helper III
6 years ago

date comparison

 

Hello 

 

Stuck with a problem. Appreciate anyone who can help!!!

 

Want to create thist table. I have the date table and # of customers. Need help to get ave. # customer and difference. 


DATEADD

3 Replies

    • nardtmo's avatar
      nardtmo
      Helper III
      6 years ago

       

      Greg,

       

      Basically i want to use Week 28-30 as my comparison week/weekday for succeeding days.  Say today is 8/28 friday, it will take friday data of week 28-30, average it and compare is to 8/28 friday data.

       

      I was able to do it with a slicer with selected date or value (see measure below) but what i wanted to do is plot the % change every day starting from WK31 (comparing the day/s to average of the same day of week 28-week 30) 

       

      Hope this clarify things. thanks in ADVANCE!!!

       

      Customer %Change =

      VAR WK27 = CALCULATE([LTE DL Volume],
      Filter(all('Date Table'),
      'Date Table'[week]=27 &&
      'Date Table'[week day]= SELECTEDVALUE('Date Table'[week day])
      ))
      VAR WK28 = CALCULATE([LTE DL Volume],
      Filter(all('Date Table'),
      'Date Table'[week]=28 &&
      'Date Table'[week day]= selectedvalue('Date Table'[week day])
      ))
      VAR WK29 = CALCULATE([LTE DL Volume],
      Filter(all('Date Table'),
      'Date Table'[week]=29 &&
      'Date Table'[week day]= selectedvalue('Date Table'[week day])
      ))
      VAR #ave Customer = CALCULATE((wk28+wk29+wk30)/3)
      return
      Divide ([Sum of customer]-#ave Customer,#ave Customer,0)

       

       weekdaydate# Customer
      Baseline period27Monday29-Jun10
      Baseline period27Tuesday30-Jun10
      Baseline period27Wednesday1-Jul10
      Baseline period27Thursday2-Jul10
      Baseline period27Friday3-Jul10
      Baseline period27Saturday4-Jul10
      Baseline period28Sunday5-Jul10
      Baseline period28Monday6-Jul10
      Baseline period28Tuesday7-Jul10
      Baseline period28Wednesday8-Jul10
      Baseline period28Thursday9-Jul10
      Baseline period28Friday10-Jul10
      Baseline period28Saturday11-Jul10
      Baseline period29Sunday12-Jul10
      Baseline period29Monday13-Jul10
      Baseline period29Tuesday14-Jul10
      Baseline period29Wednesday15-Jul10
      Baseline period29Thursday16-Jul10
      Baseline period29Friday17-Jul10
      Baseline period29Saturday18-Jul10
      Baseline period30Sunday19-Jul10
      Baseline period30Monday20-Jul10
      Baseline period30Tuesday21-Jul10
      Baseline period30Wednesday22-Jul10
      Baseline period30Thursday23-Jul10
      Baseline period30Friday24-Jul10
      Baseline period30Saturday25-Jul10
      Baseline period31Sunday26-Jul10
      Baseline period31Monday27-Jul10
      Baseline period31Tuesday28-Jul10
      Baseline period31Wednesday29-Jul10
      Baseline period31Thursday30-Jul11
      Baseline period31Friday31-Jul12
      Baseline period31Saturday1-Aug13
      Future Data32Sunday2-Aug14
      Future Data32Monday3-Aug15
      Future Data32Tuesday4-Aug16
      Future Data32Wednesday5-Aug17
      Future Data32Thursday6-Aug18
      Future Data32Friday7-Aug19
      Future Data32Saturday8-Aug20
      Future Data33Sunday9-Aug21
      Future Data33Monday10-Aug22
      Future Data33Tuesday11-Aug23
      Future Data33Wednesday12-Aug24
      Future Data33Thursday13-Aug25
      Future Data33Friday14-Aug26
      Future Data33Saturday15-Aug27
      Future Data34Sunday16-Aug28
      Future Data34Monday17-Aug29
      Future Data34Tuesday18-Aug30
      Future Data34Wednesday19-Aug31
      Future Data34Thursday20-Aug32
      Future Data34Friday21-Aug33
      Future Data34Saturday22-Aug34
      Future Data35Sunday23-Aug35
      Future Data35Monday24-Aug36
      Future Data35Tuesday25-Aug37
      Future Data35Wednesday26-Aug38
      Future Data35Thursday27-Aug39
      Future Data35Friday28-Aug40

       

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    nardtmo , you refer to my week by week blog. How to use week Rank to this week vs last week

     

    example

    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)	
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

     

    Blogs : https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

     

    How to get a  week calendar:Any Weekday Week
    https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482