Forum Discussion

alxmti's avatar
alxmti
Frequent Visitor
9 years ago
Solved

Cumulative average by date filter YTD

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 ...
Need Help
  • CahabaData's avatar
    CahabaData
    9 years ago

    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.

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
alxmti
Frequent Visitor
9 years ago

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])