Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
hi @Anonymous
Yes, you could try this logic to create a measure
Yes
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
Here is the data I need to get the streak for.
Date | Result | Category | Target |
December-06-19 | 0.915718 | Service Level | 0.8 |
December-05-19 | 0.862132 | Service Level | 0.8 |
December-04-19 | 0.794658 | Service Level | 0.8 |
December-03-19 | 0.78 | Service Level | 0.8 |
December-02-19 | 0.76 | Service Level | 0.8 |
November-29-19 | 0.92779 | Service Level | 0.8 |
November-28-19 | 0.889925 | Service Level | 0.8 |
November-27-19 | 0.778535 | Service Level | 0.8 |
November-26-19 | 0.78 | Service Level | 0.8 |
November-25-19 | 0.845833 | Service Level | 0.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
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
Date | Result | Category | Target | Weekly streak |
December-06-19 | 0.915718 | Service Level | 0.8 | 1 |
December-05-19 | 0.862132 | Service Level | 0.8 | 0 |
December-04-19 | 0.794658 | Service Level | 0.8 | -2 |
December-03-19 | 0.78 | Service Level | 0.8 | -1 |
December-02-19 | 0.76 | Service Level | 0.8 | 0 |
November-29-19 | 0.92779 | Service Level | 0.8 | 1 |
November-28-19 | 0.889925 | Service Level | 0.8 | 0 |
November-27-19 | 0.778535 | Service Level | 0.8 | -1 |
November-26-19 | 0.78 | Service Level | 0.8 | -1 |
November-25-19 | 0.845833 | Service Level | 0.8 | 0 |
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.
I created this relationship between the table based on your sample data and the calendar table:
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |