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 filtered date (in a card visual).

 

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

 

PlantDateCampaign DayProcessing
A01.01.201711000
B01.01.20171500
C02.01.20171100
A02.01.20172900
B02.01.20172400
C03.01.20172200

 

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

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.

3 Replies

  • CahabaData's avatar
    CahabaData
    Icon for Memorable Member rankMemorable Member
    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])