Cumulative average by date filter YTD

[alxmti]

Hello people,

I am trying for two days, without succes. Search the forums here a lot, it helped but still not reaching my final goal.

Target is to display a YTD Average processed for a specific filtered date (in a card visual).

I have a table like this (lets call it 'data'😞

Plant	Date	Campaign Day	Processing
A	01.01.2017	1	1000
B	01.01.2017	1	500
C	02.01.2017	1	100
A	02.01.2017	2	900
B	02.01.2017	2	400
C	03.01.2017	2	200

I made two measure for and calculated fairly easy the cumulative processed and cumulative days.

1. Cumulative processed

2. Cumulative days

Also the YTD avg. processed I calculated via

SUMX(FILTER(VALUES(data[Plant]);[Cumulative processed]/[Cumulative Days]);[Cumulative processed]/[Cumulative Days])

in order to get the sum of total averages.

Issue: when filtering on a date (for example 03.01.2017), because A and B plants have no more processing, the YTD is not anylonger calculating average processed for all, but only for Plant C and only for the day in filter.

I then tried with:

CALCULATE([Cumulative processing]/[Cumulative Days];FILTER(DimDate;DimDate[Date]>=MIN(DimDate[Date]));FILTER(DimDate;DimDate[Date]<=MAX(DimDate[Date])))

but this doesn't return the sum of total averages, meaning total processed YTD for all A+B+C.

Hope I was clear

How can I make this work?

Thanks!

Alex

[v-jiascu-msft]

@alxmti,

Hi Alex,

Did you mean that the result would be the total of the latest average of each plant even you choose the date "03.01.2017"?

It would be 633.33 + 300 + 100 = 1033.33. Right? What is formula of [Cumulative processed] and [Cumulative Days]?

Best Regards!

Dale

[alxmti]

Dale,

formulas are as follow:

Cumulative Processing = CALCULATE(Sum(data[Processing]);FILTER(ALL(DimDate[Date]);DimDate[Date] <= Max(DimDate[Date])))

Cumulative Days = CALCULATE(COUNT(data[Processing]);FILTER(ALL(DimDate[Date]);DimDate[Date] <= Max(DimDate[Date])))

the table for processing and days retrieves the cumulative correctly when the filter for Date is active (ex. 3/1/2017).

The issue appears when trying to calculate the YTD avg. processed per day with this formmula:

SUMX(FILTER(VALUES(data[Plant]);[Cumulative Processing]/[Cumulative Days]);[Cumulative Processing]/[Cumulative Days])

This returns the SUM of all plants averages per day (which I want) but it only returns for the Plant where it finds data (Plant C).

EDIT: solved it with solution from CahabaData, thanks!!! I needed an ALLSELECTED in the average calculation:

SUMX(FILTER(ALLSELECTED(data[Plant]);[Cumulative Processing]/[Cumulative Days]);[Cumulative Processing]/[Cumulative Days])

[CahabaData · Accepted solution]

a rushed reply;  in your DAX I don't see an ALL function   (or one of its variations ALLSELECTED) ....  you need that in your filter clause to break out of the row context and collect data from all rows...    there's some good Cumulative videos at Enterprise DNA N.Zealand and lot's of dialog on this.