Get all values over a treshhold in a period of time

[mlanda]

We have a data usage for cellphones table. And we need to calculate the probability of a user exceeding a 20gb usage per month. I need to traverse the past 3 months and everywhere the " Domestic Data Total " is over 20 it will add to the probability. So if a user has , 5, 21, and 7 gb , the probability is 33%. If its 22,36,7 the probability is 66% and so on. I can get the total of the months and the average but setting up the variable and adding to it using a filter is where im stuck. This is what I have so far:

Spike Probability = CALCULATE(SUM('Query1'[Value]),
	'Query1'[Attribute] IN { "DomesticDataTotal" } , DATEADD(Query1[CycleDateEnd], -3, MONTH))

This just gets the total domestic data in the last 3 months. How do I add that filter and counter to calculate the probability?

Thank you.

[v-yulgu-msft · Accepted solution]

Hi @mlanda,

Suppose source table is like:

Please refer to belowsteps applied in Query Editor mode.

Then, in data view, please add calculated columns:

Total usage per month =
CALCULATE (
    SUM ( Query1[Useage] ),
    ALLEXCEPT ( Query1, Query1[CycleDateEnd].[Month], Query1[user] )
)

Spike Probability =
CALCULATE (
    DISTINCTCOUNT ( Query1[Index] ),
    FILTER (
        ALLEXCEPT ( Query1, Query1[user] ),
        Query1[Index] <= EARLIER ( Query1[Index] )
            && Query1[Index]
                >= EARLIER ( Query1[Index] ) - 2
            && Query1[Total usage per month] > 20
    )
)
    / 3

Best regards,

Yuliana Gu

[mlanda]

Thank you so much for your response, that is a very clever way of doing it I appreciate it that you did so much to answer my question. We are currently testing it in our end.