cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cfpvin Helper III
Helper III

How to get score for 3 consecutive days off target

Hello,

 

I want to get a score of 0 if the result date is greater than the target date for three consecutive days of the week. Is this possible?

 

Thanks.

8 REPLIES 8
Super User III
Super User III

Re: How to get score for 3 consecutive days off target

Yes

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
cfpvin Helper III
Helper III

Re: How to get score for 3 consecutive days off target

@TomMartens 

 

Would you be able to explain how this can be done?

 

Thanks.

Super User III
Super User III

Re: How to get score for 3 consecutive days off target

Hey @cfpvin ,

 

I'm able to explain how to get what you want and happy to so, if you provide more details.

Please provide sample data that allows to identifiy the consecutive chain of events (3 consecutive days off target).

This sample data has to reflect your data model (at least the part that is ued to answer this specific question).

Upload the pbix and or xlsx to onedrive or dropbox and share the link.

 

Regards,

Tom

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Community Support
Community Support

Re: How to get score for 3 consecutive days off target

hi @cfpvin 

Yes, you could try this logic to create a measure

 

Measure =
var _lastrow=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<MAX('Table'[Date])))
var _nextrow=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]>MAX('Table'[Date])))
var _lastoutlier=CALCULATE([Outlier],FILTER(ALL('Table'),'Table'[Date]=_lastrow))
var _nextoutlier=CALCULATE([Outlier],FILTER(ALL('Table'),'Table'[Date]=_nextrow)) return

IF(OR([Outlier]*_lastoutlier<>BLANK(),[Outlier]*_nextoutlier<>BLANK()),0,999)
 
999 is your other output expression.
For example:
6.JPG
and here is sample pbix file, please try it.
 
Regards,
Lin
Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
cfpvin Helper III
Helper III

Re: How to get score for 3 consecutive days off target

@TomMartens 

 

Here is the data I need to get the streak for.

Date      Result CategoryTarget
December-06-190.915718Service Level0.8
December-05-190.862132Service Level0.8
December-04-190.794658Service Level0.8
December-03-190.78Service Level0.8
December-02-190.76Service Level0.8
November-29-190.92779Service Level0.8
November-28-190.889925Service Level0.8
November-27-190.778535Service Level0.8
November-26-190.78Service Level0.8
November-25-190.845833Service Level0.8

 

Thanks

Super User III
Super User III

Re: How to get score for 3 consecutive days off target

Hey @cfpvin ,

 

thanks for providing the data, but I still have a question, what is the expected result.

From my understanding there are no 3 consecutive days where the result is greater than the target.

What do you expect in this case?

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
cfpvin Helper III
Helper III

Re: How to get score for 3 consecutive days off target

@TomMartens 

 

In this case the result should be less than the target. For weekly streak I mean like if the result is greater than target, then 1 point is added to each day so the maximum streak for the week would be 5. And also if the streak is at 2 and then the result is below target, the it would be 0 and the -1 the next day if the result is still less than target. Here is how the data should look like at the end. Every first day of the week it should be 0 and then the streak should start from there depending on whether the result is less than or greater than the target

 

DateResultCategoryTargetWeekly streak
December-06-190.915718Service Level0.81
December-05-190.862132Service Level0.80
December-04-190.794658Service Level0.8-2
December-03-190.78Service Level0.8-1
December-02-190.76Service Level0.80
November-29-190.92779Service Level0.81
November-28-190.889925Service Level0.80
November-27-190.778535Service Level0.8-1
November-26-190.78Service Level0.8-1
November-25-190.845833Service Level0.80
Super User III
Super User III

Re: How to get score for 3 consecutive days off target

Hey @cfpvin 

it took me some time to get back to this problem, but here is my solution.

 

First I created a calendar table using this DAX statement:

 

Calendar = 
var DateStart = DATE(2019 , 1 , 1)
var DateEnd = DATE(2019 , 12 , 31)
return
ADDCOLUMNS(
    ADDCOLUMNS(
        CALENDAR(DateStart , DateEnd)
        , "Iso Week" , WEEKNUM(''[Date] , 2)
        , "Start of Week" , ''[Date]  - WEEKDAY(''[Date] , 2) + 1
        , "End of Week" , ''[Date] + 7 - WEEKDAY(''[Date] , 2)
    )
    , "Day in Week" , DATEDIFF([Start of Week] , [Date] , DAY) + 1
)

 

This is a little screenshot of the calendar table, please be aware, that there is the assumption that the week starts on Mondays.

image.png

I created this relationship between the table based on your sample data and the calendar table:

image.png

I created the explicit measures Total Result and Total Target using these DAX statements:

 

Total Result = SUM('Table'[Result])

 

and

 

Total Target = SUM('Table'[Target]) 

 

Then I created the measure change to return 1 if Total Result is greater than Total Target and -1 otherwise, using this DAX statement

 

change = 
IF(NOT(ISBLANK([Total Result]))
    , IF(CALCULATE([Total Result] - [Total Target]) > 0  , 1 , -1)
    , BLANK()
)

 

Then I created the measure trend, this measure indicates if there has been a change regarding the previous period:

 

trend = 
IF(NOT(ISBLANK([Total Result]))
    , var _now = IF(CALCULATE([Total Result] - [Total Target]) > 0  , 1 , -1)
    var _then = IF(CALCULATE(
        [Total Result] - [Total Target]
        , FILTER(ALL('Calendar')
            , 'Calendar'[Date] = MAX('Calendar'[Date]) - 1)
        ) > 0  , 1 , -1)
    return
    IF(_now <> _then , "change" , "no change")
)

 

Then I created the measure check, this measure sets the sequence of consecutive days to zero:

 

check = 
IF(HASONEVALUE('Table'[Date      ]) 
    , sumx(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table'
                , 'Table'[ Category]
                , 'Calendar'[Date]
                , 'Calendar'[Day in Week]
            )
            , "trend" , [trend]
            , "change" , [change]
        )
        , var _thisorthat =
            SWITCH(
                [Day in Week]
                , 1 , 0
                , 2 , BLANK()
                , IF([trend] = "change" , 0 , BLANK())
            )
        
        return
        _thisorthat
    )
    , BLANK()
)

 

The final measure consecutive days is your "weekly streak".

 

consecutive days = 
IF(AND(NOT(ISBLANK([Total Result])) , HASONEVALUE('Calendar'[Date]))
    ,var _check = CALCULATE([check])
    var last0 = CALCULATE(
                LASTNONBLANK('Calendar'[Date] , [check])
                , FILTER(
                    ALL('Calendar'[Date])
                    , 'Calendar'[Date] < MAX('Calendar'[Date])
                )
            )
    return
    IF(_check == 0 
        , 0 
        , DATEDIFF(last0 , MAX('Calendar'[Date]) , DAY) * [change]
    ) 
    , BLANK()
)

 

This allows to create this table:

image.png

As you see, the final measure consecutive days returns a different result on the 27th of November 2019, please explain why your expected result is -1 and not -2 as my measure returns for this day.

 

Regards,

Tom

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors