Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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]
)*/
)
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
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |