DAX Iterator Behavior

Question

I don't think this is doing what I want it to do. I have a fact table with columns date, weekstarting, customer, product and qtyshipped and I want an average product sales including dates with 0 sales. The 0 sales dates don't exist in the table. If I use an iterator does this do what I think its doing? I've grouped by week.

AVERAGEX(VALUES(facttable[weekstarting]),SUM(fact[qtyshipped]))

If this doesn't work this way is there a way to make a measure that would account for these 0s without editing the fact table? I can't seem to find a function that would do this.

You want the average per day including all the dates without sales? — Daniel A. Gregersen, Apr 22 '22 at 11:08
Yes, so if i happen to have 3 weeks with sales and 1 week without I want the denominator to be 4. But its only 3. I realize I can sort of get around this by hard coding / using a variable for the denominator but I also want to get stdev.p to work and that's harder to hard code with dax. I dug into it and the way I wrote this formula it definitely doesn't work but I don't know if I could write an averagex or other function that would work. — getunstuck, Apr 23 '22 at 00:28

score 0 · Answer 1 · answered Apr 23 '22 at 05:28

It'd just bypass the average function and use a SUM and divide by DATEDIFF set to days, weeks or what ever you want the average of.

DIVIDE(
    SUM( fact[qtyshipped] ),
    DATEDIFF(
        facttable[weekstarting],
        facttable[weekending],
        WEEK
    )
)

DAX Iterator Behavior

1 Answers1