cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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:

1 ACCEPTED SOLUTION
Community Support

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 =
'Table',
"Check",
IF (
&&
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 2
Community Support

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 =
'Table',
"Check",
IF (
&&
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.

Frequent Visitor

@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 =
"Check",
IF (
[Total OOS D-A/Date (NS) *NB*] <= [Threadhold]
&&
VAR _NEXT =
MINX (
)
RETURN
MAXX (
[Total OOS D-A/Date (NS) *NB*]
1,
0
)
)
RETURN
SUMX ( _Check, [Check] )

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors