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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-lili6-msft
Community Support
Community Support

hi @Anonymous 

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.
TomMartens
Super User
Super User

Yes



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens 

 

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

 

Thanks.

Hey @Anonymous ,

 

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@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

Hey @Anonymous ,

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@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

Hey @Anonymous 

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

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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