Calculate Average of a Measure by Year to Date

[mfp32]

Hi!

Very new to PBI! Looking for guidance / help how to do the following logic.

I have a table as shown below. I need to add a new measure called YTD ATT that averages Year-to-Date values of the measure called MONTHLY ATT.

For example:

January 2019, the YTD Att would be 0.1448

In February, it should be 0.1449 (average of January and February MONTHLY ATT)

In March, it should be 0.1591 (average of January to March MONTHLY ATT)

In December, it would have been the average of all MONTHLY ATT values of 2019.

Then for January 2020, it would repeat again.

Thank you!

[agage5]

Hello, I tried to follow your logic but I am getting an error.  The MAX function only accepts a column reference as an argument.

In my SAP scrap actuals table I have quantity actuals for FY23/FY24 periods 1-12 in FY23, and 1-6 in FY24.  I am trying to get the average of all of those months for quantity.  So it would be YTD period 6 would be the history of 18 months for quantity - one number for YTD.  I added the month name.  My month number = period for my table.  I added a quantity measure which is this 

Quantity measure = CALCULATE(sum(SAP_Scrap_Actuals[Quantity])).  FY = Year

DAX below getting error.

YTD ATT = AVERAGEX(FILTER(ALLSELECTED(SAP_Scrap_Actuals),SAP_Scrap_Actuals[FY]=max(SAP_Scrap_Actuals[FY]&&SAP_Scrap_Actuals[Period]=max(SAP_Scrap_Actuals[Period])),[Quantity measure]))

[v-stephen-msft · Accepted solution]

Hi @mfp32 ,

Since I don't know what your raw data is. I made sample data on my own.

You could see the column of month numbers in my sample data. You need to add a column of month numbers to your table too. You can get the month number using the MONTH function. Or replace the text month with a numeric month with the SWITCH function.

I also created a metric to return to Monthly ATT.

Then the YTD ATT could be created like

YTD ATT = AVERAGEX(FILTER(ALLSELECTED('Table'),[Year]=MAX('Table'[Year])&&[Month Number]<=MAX('Table'[Month Number])),[Monthly ATT])

If you want to round down, add ROUNDDOWN function.

YTD ATT = ROUNDDOWN(AVERAGEX(FILTER(ALLSELECTED('Table'),[Year]=MAX('Table'[Year])&&[Month Number]<=MAX('Table'[Month Number])),[Monthly ATT]),4)

Best Regards,

Stephen Tao

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

[AzizQuazi]

Dear Stephen Tao,
Thanks for the PBIX file. It was an excellent learning for me as I faced the same issue. However, when I changed the visual to show the Monthly and YTD ATT under the same graphic, the YTD value changed to the Monthly value due to the changes in the context, as shown in the screenshot below. Here is the link to the same PBIX file with the new visual on the next page - It would be great if you could help me with what to change in the YTD measure in the contemporary context. The PBIX File is attached. Thanks. 

[AzizQuazi]

Hi Stephen Tao,
Thanks for your PBIX file to explain this. I am having the same proplem but this solution is not working for my data model - not sure where I am doing wrong. I wanted to attach the PBIX file and an excel file to show what I want but looks like I cannot attach any file - any idea why I cannot attach any file?

Anyway, I have a transaction table and also a date table in my mdel and I have created the foowing measures:

1. 

Total Value = SUM('Transaction Data'[USD])

2. 

_ACT_MTD Average Active Agents = CALCULATE([Total Value], 'Item'[Item] = "Average Active Agents")

3. 

_ACT_YTD Average Active Agents = CALCULATE([_ACT_MTD Average Active Agents], DATESYTD('Date'[Date]))

4. Solotion 1 to calculate average

__Solution_StephenTao = (AVERAGEX(FILTER(ALLSELECTED('Date'),[Year]=MAX('Date'[Year])&&[Month NO]<=MAX('Date'[Month NO])),[_ACT_YTD Average Active Agents]))
5. Solution 2 

   _Solution_Greg =

  VAR __MaxDate = MAX('Date'[Date])

  VAR __Year = YEAR(__MaxDate)

  VAR __Table = SUMMARIZE(FILTER('Date', YEAR([Date]) = __Year && [Date] <= __MaxDate),'Date'[Month NO],"__MonthlyAtt",[_ACT_YTD Average Active Agents])

RETURN

  AVERAGEX(__Table, [__MonthlyAtt])

 I am getting the following report - average calculatrion is not showing the right value:

The Feb vaue should be 13,101 + 13,901 = 27,002 / 2 = 13,501

The March value should be 13,101 + 13,901 + 17,656 = 44,658 / 3 = 14,884
Would you be able to help where I am going wong? Thanks.

@Greg_Deckler , you may like to help also how to write variables in this case - I tried too abve but somehow not getting the expected result. 

Appreciate help from the community. Thanks.

Rgds...Aziz

[AzizQuazi]

@v-stephen-msft and @Greg_Deckler 

here is another screensht - more clearer 

[AzizQuazi]

@Greg_Deckler ,

Basically, the The Table variable is generating an empty table as below:

[AzizQuazi]

Here is the link to the PBIX file - hope you can access it:

https://1drv.ms/u/s!AgFUe-XGc3QMg9ML54it-XuUgMF8fQ?e=cAD1JJ
and an excel example file Troubleshoot Averge.xlsx

@v-stephen-msft and @Greg_Deckler 

[AzizQuazi]

Hi @v-stephen-msft and @Greg_Deckler 

Finally, I have managed this:

__YTD Average =

CALCULATE(

    AVERAGEX(    

        SUMMARIZE('Date',

    'Date'[Year], 'Date'[Month NO]),

    [_ACT_YTD Average Active Agents]), FILTER(ALLSELECTED('Date'), 'Date'[Year] = MAX('Date'[Year]) && 'Date'[Month NO] <=MAX('Date'[Month NO])))

[Greg_Deckler]

@mfp32 Maybe:

YTD Measure =
  VAR __MaxDate = MAX([Date])
  VAR __Year = YEAR(__MaxDate)
  VAR __Table = SUMMARIZE(FILTER('Table', YEAR([Date]) = __Year && [Date] <= __MaxDate),[Month],"__MonthlyAtt",[MONTHLY ATT])
RETURN
  AVERAGEX(__Table, [__MonthlyAtt])