Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
18 | |
17 |
User | Count |
---|---|
33 | |
25 | |
18 | |
15 | |
13 |