Changing Filter Context in Measure

[phjz]

Hey guys,

I have a measure that sums up values per week and multiplies them with a factor:

Actuals = Sumx(SUMMARIZE(Channels,Channels[Category]),sum('Actuals'[Value]) * (1 + min('Factors'[Value])))

Now, I want to calculate a YTD, which averages all Actuals until a selected calendar week. But, when I try this formula:

YTD = CALCULATE(AVERAGEX(SUMMARIZE('Actuals','Actuals'[CW],"Weekly",[Actuals]),[Weekly]),FILTER(all(Calendar),Calendar[Year] = MAX(Calendar[Year]) && Calendar[Date] <= MAX(Calendar[Date])))

...it doesn't work, because it seems, that DAX chooses the minimum of all factors for every single value before averaging. But I want it to apply a certain factor for every week and after that average the values.

Does anybody have an idea?

Thanks! 

[v-rongtiep-msft · Accepted solution]

Hi @phjz ,

Please have a try.

YTD Actuals Average = 
VAR CurrentYear = MAX(Calendar[Year])
VAR LastDate = MAX(Calendar[Date])
RETURN
AVERAGEX(
    FILTER(
        SUMMARIZE('Calendar', Calendar[Year], Calendar[Date], "WeekActuals", [Actuals]),
        Calendar[Year] = CurrentYear && Calendar[Date] <= LastDate
    ),
    [WeekActuals]
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft · Accepted solution]

Hi @phjz ,

Please have a try.

YTD Actuals Average = 
VAR CurrentYear = MAX(Calendar[Year])
VAR LastDate = MAX(Calendar[Date])
RETURN
AVERAGEX(
    FILTER(
        SUMMARIZE('Calendar', Calendar[Year], Calendar[Date], "WeekActuals", [Actuals]),
        Calendar[Year] = CurrentYear && Calendar[Date] <= LastDate
    ),
    [WeekActuals]
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[phjz · Accepted solution]

this works perfectly - thank you so much!

[3CloudThomas]

Then, you need a COUNT measure and then DIVIDE the 2 measures.

[3CloudThomas]

Actuals YTD = TotalYTD( [Actuals], Calendar[Date]
Make sure your Calendar table is Marked as Date Table

[phjz]

Thanks for your reply. It seems that TOTALYTD sums up the values, but I want them to calculate an average. Do you have an idea for this as well?