Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Weighted average with dynamic total based on filter

Hello I am trying to find a weighted average where the average changes based on a date heirarchy filter; The filter is an individual date and start of week.   For example, if I am viewing a ...
  • amitchandak's avatar
    3 years ago

    Anonymous , Create a date table with week columns

     

    Have these new columns in Date Table, Week Rank is Important in Date/Week Table
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
    WeekDay = weekday([Date])

     

    These measures can help
    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

     

     

    Avg of Week  =CALCULATE(Averagex(Values(Date[Date]),CALCULATE(sum('Table'[Qty]))) , FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s