how to make measure sum dynamically drilling up from month to year

[alya1]

Hi all,

I created this forecasting measure that is a 12 month average for future months. 

But when I put it on a visual along with historical data, the historical amounts are summed the higer one drills up (Years) but the 12 month average does not.

How can I make the 12 month average measure dynamically sum based on x-axis? 1 year would be 12 of the 12 months ag added up

thank you

[v-jtian-msft · Accepted solution]

Hi,@alya1 I am glad to help you.

According to your description, you have successfully calculated the value of measure, but when adding it to the chart, since the date column will be displayed in year, you also want to make the measure also display the result in year, instead of calculating it by month in measure.

I think you can use the IF function to make a judgment on the field, and when you determine that the VISUAL that the MEASURE is in is filtered by the year, then multiply it by the number of months in the corresponding year, otherwise display it according to the original value

If my understanding is correct. You can refer to my test idea below
here is my test data: (Daily sales from January 2023 through January 2025, only 1 month in 2025)
You can use the HASONEVALUE() function as a judgment condition

Get the number of months in each year in the data table

M_yearnum = 
CALCULATE(
DISTINCTCOUNT('Table'[MonthNum]),FILTER(ALLSELECTED('Table'),'Table'[YearNum]=MAX('Table'[YearNum])))

The final Measure: Determine if the placed visual is modified by the year, if so multiply the original measure calculation by the number of months in the year obtained, otherwise display the original measure.

M_result = 
VAR leastday=CALCULATE(MAX('Table'[Date]),ALL('Table'))
//leastday:The leastDate in the table
var last12month=EOMONTH(leastday,-12)+1
VAR yearnum=YEAR(MAX('Table'[Date]))
VAR monthnum=MONTH(MAX('Table'[Date]))
VAR value1_=
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),YEAR('Table'[Date])=yearnum&&MONTH('Table'[Date])=monthnum 
&&'Table'[Date]>=last12month&&'Table'[Date]<=leastday))*[M_yearnum]
VAR value2_=
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),YEAR('Table'[Date])=yearnum&&MONTH('Table'[Date])=monthnum 
&&'Table'[Date]>=last12month&&'Table'[Date]<=leastday))
// value2_:Return total sales for the latest month of the year

RETURN 
IF(HASONEVALUE('Table'[Date].[Year]),
value1_,value2_)

The measure I created only calculates data for the latest year time (February 2024 through January 2025), so there is no corresponding M_result data for 2023

Hopefully my tests will give you good ideas!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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