Forum Discussion

dolevh's avatar
dolevh
Icon for Helper II rankHelper II
5 years ago
Solved

Average 4 week same week day

Hi everyone, I want to produce a function that will make me an average of the number of users each month of all days of the week (Sunday until Saturday) For example: The average of Sundays in July...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    dolevh So, have to make some assumptions because no sample source data, but perhaps something like:

    Measure = 
  VAR __Date = MAX('Table'[Date)
  VAR __Weekday = WEEKDAY(__Date)
  VAR __Month = MONTH(__Date)
  VAR __Year = YEAR(__Date)
  VAR __Table = FILTER(ALL('Table'),[Year]=__Year && [Month]=__Month && [Weekday]=__Weekday)
RETURN
  DIVIDE(
    SUMX(__Table,[Value]),
    COUNTROWS(__Table),
    0
  )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

dolevh So, have to make some assumptions because no sample source data, but perhaps something like:

Measure = 
  VAR __Date = MAX('Table'[Date)
  VAR __Weekday = WEEKDAY(__Date)
  VAR __Month = MONTH(__Date)
  VAR __Year = YEAR(__Date)
  VAR __Table = FILTER(ALL('Table'),[Year]=__Year && [Month]=__Month && [Weekday]=__Weekday)
RETURN
  DIVIDE(
    SUMX(__Table,[Value]),
    COUNTROWS(__Table),
    0
  )