Forum Discussion

snaseem's avatar
snaseem
Helper I
3 years ago
Solved

Counting Spikes in Line Chart

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 measu...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

     

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

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's avatar
CD_75
Frequent Visitor
2 years ago

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] )