Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
snaseem
Helper I
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:

snaseem_0-1666188475387.png

 

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @snaseem ,

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

vkalyjmsft_0-1666234350301.png

vkalyjmsft_1-1666234409431.png

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.

vkalyjmsft_2-1666234457966.png

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.

 

View solution in original post

2 REPLIES 2
v-yanjiang-msft
Community Support
Community Support

Hi @snaseem ,

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

vkalyjmsft_0-1666234350301.png

vkalyjmsft_1-1666234409431.png

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.

vkalyjmsft_2-1666234457966.png

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

 

Times OOS.png

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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