YTD Average of a Cumulative Measure

[modelmike]

I'm struggling and need some help. Per another post, I created a measure that counts the number of unique customers that exist in a 13 week period prior to DimWeek[Week_Num]. This is a cumulative measure because it calculates total distinct values in the time period. Now, I want to create a measure that calculates the YTD average of this L13W measure. We'll call this new measure "ACT_POCS_YTDAVG"

Code for the existing L13W Measure.

ACT_POCS_L13Wchart = 
   Calculate(
       DISTINCTCOUNT(Sales_Data[Customer]), 
          FILTER(ALLSELECTED(Sales_Data),
              Sales_Data[Week_Num]<=Sales_Data[Week_Num]) &&
              Sales_Data[Week_Num]>=MAX(DimWeek[Week_Num])-13)
            )
       

Table showing desired value of ACT_POCS_YTDAVG

The DISTINCTCOUNT makes this difficult for me. Is there any way to make a measure to do this, or do I need to convertACT_POCS_L13W to a calculated column instead?

[v-juanli-msft]

Hi @modelmike

I test with your example shown on the picture

Create measures

Measure2 = MAX([Week_num])

Measure3 = CALCULATE(SUMX(FILTER(ALL(Sheet1),[Week_num]<=MAX([Week_num])),[Measure1]))/[Measure2]

Best regards

Maggie

[modelmike]

Hi @v-juanli-msft Maggie,

Thank you very much for assisting. I've been smashing my head against the wall trying to make this work. 

I adapted the code you gave me. I can't get this measure to work in the report because of general performance. When I try to add this measure to a card/table, it basically stalls my whole PBI. I see the little "thinking" circle in the top left of the object and then PBI just sits there. Is this because the SUMX formula is adding significant complexity due to size of my Sales_Data table?

Code:

MAXWeek = MAX(Sales_Data[Week_Num])

ACT_POCS_YTDAVG = 
     CALCULATE(
        SUMX(
            FILTER(ALL(Sales_Data),
                SalesData[Week_Num]<MAX(DimWeek[Week_Num]))
            ,_distribution[ACT_PODS_L13WChart]) --Original Measure aka Measure 1
    )
    / _distribution[MAXWeek] --aka Measure 2
       

[v-juanli-msft]

Hi @modelmike

Yes, you are right, the SUMX function might affect the execution performance, especially nested SUMX.

You could refer to below blogs to optimizie DAX formula.

Tune your powerpivot dax query don’t use the entire table in a filter and replace sumx if possible

Nested sumx or dax query

Best Regards

Maggie