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.
Need your expertise on this one.
I'm trying to create a measure that will calculate total days it took them to achieved the goal (the ones highlighted in green) but it should be in the first 2 consecutive weeks before they will be considered as passers. Like for example on the below dax table, looking at the first student named "Ampo", Ampo were able to achieved the goal in the frst 2 consecutive weeks in days 83 and 90 respectively. I only need the tenure in days of the first week they achieved the goal; therefore, the output s/b 83 days just for Ampo. Again, the student should get the passing score for at least two consecutive weeks.
Output other students:
Cabarrubias: days 69 and 76. output s/b 69
Cuico: days 76 and 83. output s/b 76
Your help is greatly appreciated.
Solved! Go to Solution.
Hi @MacyMolly ,
Please check:
Method 1: Don't use week number.
1. Create measures like so:
Conditional formatting Measure =
IF ( AVERAGE ( 'Table'[Value] ) >= 72, 1, 0 )
Day Diff =
VAR NextDays_ =
IF (
[Conditional formatting Measure] = 1,
CALCULATE (
MIN ( 'Table'[Days] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& 'Table'[Days] > SELECTEDVALUE ( 'Table'[Days] )
&& [Conditional formatting Measure] = 1
)
)
)
VAR DayDiff_ =
NextDays_ - MAX ( 'Table'[Days] )
RETURN
IF ( DayDiff_ = 7, 1 )
output =
CALCULATE (
MIN ( 'Table'[Days] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& [Day Diff] = 1
)
)
2. Create visuals.
Method 2: Use week number.
1. Create a Date table with "Day of Year".
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
"WeekNum", WEEKNUM ( [Date], 1 )
)
Day of Year =
DATEDIFF ( STARTOFYEAR ( 'Date'[Date] ), 'Date'[Date], DAY ) + 1
2. Create relationship between "Date" and "Table".
3. Create WeekNum column in "Table".
WeekNum =
RELATED ( 'Date'[WeekNum] )
4. Create measures.
Conditional formatting Measure =
IF ( AVERAGE ( 'Table'[Value] ) >= 72, 1, 0 )
Week Diff =
VAR NextWeekNum_ =
IF (
[Conditional formatting Measure] = 1,
CALCULATE (
MIN ( 'Table'[WeekNum] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& 'Table'[WeekNum] > SELECTEDVALUE ( 'Table'[WeekNum] )
&& [Conditional formatting Measure] = 1
)
)
)
VAR WeekDiff_ =
NextWeekNum_ - MAX ( 'Table'[WeekNum] )
RETURN
IF ( WeekDiff_ = 1, 1 )
output =
CALCULATE (
MIN ( 'Table'[Days] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& [Week Diff] = 1
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @MacyMolly ,
Please check:
Method 1: Don't use week number.
1. Create measures like so:
Conditional formatting Measure =
IF ( AVERAGE ( 'Table'[Value] ) >= 72, 1, 0 )
Day Diff =
VAR NextDays_ =
IF (
[Conditional formatting Measure] = 1,
CALCULATE (
MIN ( 'Table'[Days] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& 'Table'[Days] > SELECTEDVALUE ( 'Table'[Days] )
&& [Conditional formatting Measure] = 1
)
)
)
VAR DayDiff_ =
NextDays_ - MAX ( 'Table'[Days] )
RETURN
IF ( DayDiff_ = 7, 1 )
output =
CALCULATE (
MIN ( 'Table'[Days] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& [Day Diff] = 1
)
)
2. Create visuals.
Method 2: Use week number.
1. Create a Date table with "Day of Year".
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) ),
"WeekNum", WEEKNUM ( [Date], 1 )
)
Day of Year =
DATEDIFF ( STARTOFYEAR ( 'Date'[Date] ), 'Date'[Date], DAY ) + 1
2. Create relationship between "Date" and "Table".
3. Create WeekNum column in "Table".
WeekNum =
RELATED ( 'Date'[WeekNum] )
4. Create measures.
Conditional formatting Measure =
IF ( AVERAGE ( 'Table'[Value] ) >= 72, 1, 0 )
Week Diff =
VAR NextWeekNum_ =
IF (
[Conditional formatting Measure] = 1,
CALCULATE (
MIN ( 'Table'[WeekNum] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& 'Table'[WeekNum] > SELECTEDVALUE ( 'Table'[WeekNum] )
&& [Conditional formatting Measure] = 1
)
)
)
VAR WeekDiff_ =
NextWeekNum_ - MAX ( 'Table'[WeekNum] )
RETURN
IF ( WeekDiff_ = 1, 1 )
output =
CALCULATE (
MIN ( 'Table'[Days] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Last Name] = MAX ( 'Table'[Last Name] )
&& [Week Diff] = 1
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , i'm sorry. One more thing, if i'm not going to include days 62 and just focus on days 69 and onwards. what would be the measure? Thank you.
Hi @MacyMolly ,
Just try this:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Good day @Icey Wow! This is so Amazing! Both methods works perfectly! You just saved my ass! Thank you and Kudos!!!
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |