AverageX on a virtual table

[mattio]

Hello all,

I have spent a number of hour researching and not getting anywhere, if there is anybody that can help I would appreciate it!

I have a table of dates, which i am building a forecast measure for called 't'. I am trying to add the measure shown in grey on the picture to my table (unsuccesfully) because I cant seem to apply the correct filter to my virtual table.

Could anybody suggest code that would allow me to put the figures in grey into my visual using DAX / virtual table only.

AverageX

The code for my workbook (sample data):

let
    Source = Table.FromList(List.Dates(Date.From(DateTime.FixedLocalNow()), 14, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.SelectColumns(Table.AddColumn(Source, "t", each List.Repeat({[Column1]}, Number.RoundUp(Number.RandomBetween(50, 100)))), {"t"}),
    #"Expanded t" = Table.ExpandListColumn(#"Added Custom", "t"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded t",{{"t", type date}})
in
    #"Changed Type"

My dax calculated measure (Sx ? = where a i would like expected output to be returned:

Sx ? = //return the average of all "Sx Ix" by weekday
//t[t] = a list of dates
VAR _weekday = WEEKDAY(SELECTEDVALUE(t[t]), 2) //the weekday of each t[t]
VAR _t_by_date = //a summary of t grouped by t[t] with extra calculated a column called Sx Ix
    SUMMARIZE(
            t, t[t], "weekday", WEEKDAY(t[t]),
            "Sx Ix",
            DIVIDE(
                COUNTROWS(t), //a count of all rows grouped by t[t]
                CALCULATE(
                    COUNTROWS(t),
                    ALL(t), t[t] + 3 >= EARLIER(t[t]) && t[t] - 3 <= EARLIER(t[t])
                ) / 7 //a rolling average of 7 days
            ) //the Sx Ix calculated column
    )
RETURN
    "?" 
    /*AVERAGEX(
        FILTER(
            SUMMARIZE('t by date', 't by date'[weekday], "Sx", AVERAGE('t by date'[Sx Ix])),
            't by date'[weekday] = _Weekday
        ),
        [Sx]
    )*/

The dax calculated table with calculated column (expected output):

t by date = 
VAR _sx_ix = 
VAR _weekday = SELECTEDVALUE(t[weekday])
VAR _t_by_date = 
    SUMMARIZE(
            t, t[t], "weekday", WEEKDAY(t[t]),
            "Sx Ix",
            DIVIDE(
                COUNTROWS(t),
                CALCULATE(
                    COUNTROWS(t),
                    ALL(t), t[t]+3 >= EARLIER(t[t]) && t[t]-3 <= EARLIER(t[t])
                ) / 7
            )
    )
RETURN
ADDCOLUMNS(
    _t_by_date,
    "Sx ?",
        "?" /*"want to add formulated column here"
            VAR _Weekday = WEEKDAY('t by date'[t])
                RETURN
                    AVERAGEX(
                        FILTER(
                            SUMMARIZE('t by date', 't by date'[weekday], "Sx", AVERAGE('t by date'[Sx Ix])),
                            't by date'[weekday] = _Weekday
                        ),
                    [Sx]
                    )*/
)

[Greg_Deckler]

Sample data, expected output please. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[mattio]

Hi, yes thanks for that i did post the M code which contains the sample data as well as expected output as seen in the DAX examples.