Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Rolling Average and Variance DAX Measures

Hi, I need help creating two new measures. 

1) Rolling average of amount earned for each Site (Site can be selected via Slicer). There are 23 sites if that matters. 

2) Weekly Variance (Date can be selected via Slicer)

 

I currently have 3 tables as follows: Weekly Sales Amount (date, site, dollar amount), Date (date and # of week in the year), and site (site name). 

 

Any help creating these two DAX functions would be greatly appreciated. 

 

I tried to use the quick measure/time intelligence feature  but was met with this error: 

"Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    7 years ago

    Try Like

    Current Sales = ( 
VAR _Cuur_start = Min('Date'[Date]) -- Or choose week start date
VAR _Curr_END = Max('Date'[Date ])
VAR _WEEK_NUM = MAXX(FILTER('Date'[Date] =_Curr_END),'Date'[Week of year])
return 
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <=  _Curr_END )
)



Prior Sales = ( 
VAR _WEEK_NUM = MAXX(FILTER('Date'[Date Filer] =_Curr_END),'Date'[Week of year]) -1
return 
calculate(sum(Sales[Sales]),'Date'[WEEK OF YEAR] >= _WEEK_NUM )
)
  • Cmcmahan's avatar
    Cmcmahan
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    You should be able to mark your table Date as a date table.  

     

    This should clear up the error and allow you to move forward.  

     

    If trying to mark it as a date table gives you errors along the lines of needing unique values, then you like need a date dimension.

    Here's the power query code I use to create quick date dimensions. Be sure to update the Start and End dates as appropriate for your data.

    let
    StartDate = #date(2019,1,1),
    EndDate = #date(2019,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([Date]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([Date]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    #"Inserted Day Name"