Calculating Variation per Month

[Ade1991]

Hi,

I have a list of 35 000 sentences from which I extracted key words.

For each sentence, I have a rate and the date, from 2010 to 2019.

I would like to be able to see which are the keywords increasing/decreasing the most (variation of average rate per month) between minimum 5 differents years.

I'm a little lost to how to structure my DAX formula.

I was thinking to :

1) calculate average rate per month : I think I should use CALCULATE + AVERAGE + FILTER, but not sure how to calculate by month

2) I'm a little lost to calculate the variation

3) Display an histogram with the keyword with the TOP variation (increasing/decreasing)

I'm looking for some help to structure my think 🙂

Thanks

[v-cherch-msft · Accepted solution]

Hi @Ade1991 

It seems you may try to use ALLEXCEPT function.If it is not your case,please follow the How to Get Your Question Answered Quickly to post your simple assumed data and expected output.

Reference:https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

Measure =
CALCULATE (
    AVERAGE ( Table[Rate] ),
    ALLEXCEPT ( Table, Table[Year], Table[Month] )
)

Regards,