Counting Spikes in Line Chart

[snaseem]

Hi,

I want to compute a measure that shows monthly sales along with threshold showing a line representing X percentage of the largest value for sales amount.

Then want to create a spike measure that counts how many times sales amount has crossed the threshold.

The desired output is:

[v-yanjiang-msft · Accepted solution]

Hi @snaseem ,

According to your description, I create a sample. Threadhold is a measure whose value is 5.5.

Here's my solution. Create a measure.

Count =
VAR _Check =
    ADDCOLUMNS (
        'Table',
        "Check",
            IF (
                'Table'[Sales] <= [Threadhold]
                    &&
                    VAR _NEXT =
                        MINX (
                            FILTER ( 'Table', 'Table'[Date] > EARLIER ( 'Table'[Date] ) ),
                            'Table'[Date]
                        )
                    RETURN
                        MAXX ( FILTER ( 'Table', 'Table'[Date] = _NEXT ), 'Table'[Sales] ) > [Threadhold],
                1,
                0
            )
    )
RETURN
    SUMX ( _Check, [Check] )

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[CD_75]

@v-yanjiang-msft This might work perfectly for what I need the only issue I have is that the measure counts all dates within each occurance. I need the measure to count each time the Threadhold is reached but only once for that occurence. 

For example, a SKU goes out of stock for 5 days. It'ts recorded on the graph that it reached the Threadhold (1 , which represents 1 day out of stock). The measure works but it returns 5 and not a 1... I just need it to return the first occurance of any series of days. Any advice? 

Times OOS (*) =
VAR _Check =
ADDCOLUMNS (
'All_OOS_AD',
"Check",
IF (
[Total OOS D-A/Date (NS) *NB*] <= [Threadhold]
&&
VAR _NEXT =
MINX (
FILTER ( 'All_OOS_AD', 'All_OOS_AD'[Date] > EARLIER ( 'All_OOS_AD'[Date] ) ),
'All_OOS_AD'[Date]
)
RETURN
MAXX (
FILTER ( 'All_OOS_AD', 'All_OOS_AD'[Date] = _NEXT ),
[Total OOS D-A/Date (NS) *NB*]
) >= [Threadhold],
1,
0
)
)
RETURN
SUMX ( _Check, [Check] )