Add month of all years

[Anonymous]

Hi all PLEASE HELP :******), I need to create a dynamic measure that returns

1. the average of January of each historical year when I'm on Jan 21. When I'm in Feb 21,  return the average of Feb of each historical year etc etc

2. It has to apply an AVERAGEIF condition to only average the range that contains more than 0. E.g., in the above example, i add all the figures but only divided them by 4 instead of 5 periods. 

3. I cannot use time intelligence functions such as MONTH/Dates in periods etc because I have a custom calendar and months are defined by indexes and also month-year numerical formats (202103) 

[v-xiaotang]

Hi @Anonymous 

Can we close this thread now if there is no other question? It would be nice if you can accpet my answer as the solution. Thank you very much 🙂

Best Regards,

Community Support Team _ Tang

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

[v-xiaotang]

Hi @Anonymous 

Based on your description, I created a measure, but it needs you to add a standard date column in the data table.  well, it would be better if you can share your sample file, so that we can give you the meausre more in line with your needs.

you can try the measure like

Measure = 
    var _today=DATE(2021,1,21)//DATE(2021,2,21)  you can replace it with function to get the current date
    var _historicalMonth=MONTH(_today)
    var _historicalYear=MIN('Calendar Table'[Year])
    var _t=FILTER('Table','Table'[date]<DATE(_historicalYear,_historicalMonth+1,1)&&'Table'[date]>=DATE(_historicalYear,_historicalMonth,1))
    var _total=CALCULATE(SUM('Table'[value]),_t)
    var _countRows=CALCULATE(COUNTROWS('Table'),_t)
return _total/_countRows

if you need more help, please @ me

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi xiao tang, thank you so much for the help!!! But unfortunately this cannot work because you have used "MONTH" and i am not able to use the time intelligence functions because our calendar is different. for.e.g,  the week at the end of dec is actually in jan. 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

Thanks for your reply.

if the week at the end of dec is actually in jan, and the type of date column in your fact table is Date, then try this,  

create a calculated column,

month = IF( [date].[Day]<=21,MONTH([date]),MONTH(EOMONTH([date],1)))

create a measure,

average = CALCULATE(AVERAGE([value]),FILTER(ALL('Table'),[month]=MAX([month])&&[value]<>0))

result

Best Regards,

Community Support Team _Tang

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