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 measure that counts how many times sales amount has crossed the threshold.

 

The desired output is:

 

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.

     

2 Replies

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