Forum Discussion
Anonymous
6 years agoNot applicable
Groupings using date and current date and year
Hi Anonymous ,
To update the formula as below:
Type = VAR last1 = EDATE ( TODAY (), -12 ) VAR last2 = EDATE ( TODAY (), -24 ) VAR last3 = EDATE ( TODAY (), -36 ) VAR last4 = EDATE ( TODAY (), -48 ) VAR last5 = EDATE ( TODAY (), -60 ) VAR last6 = EDATE ( TODAY (), -72 ) VAR last7 = EDATE ( TODAY (), -84 ) VAR last8 = EDATE ( TODAY (), -96 ) VAR last9 = EDATE ( TODAY (), -108 ) VAR last10 = EDATE ( TODAY (), -120 ) RETURN SWITCH ( TRUE (), 'Table'[RegDate] <= TODAY () && 'Table'[RegDate] >= last1, "yr1", 'Table'[RegDate] < last1 && 'Table'[RegDate] >= last2, "yr2", 'Table'[RegDate] < last2 && 'Table'[RegDate] >= last3, "yr3", 'Table'[RegDate] < last3 && 'Table'[RegDate] >= last4, "yr4", 'Table'[RegDate] < last4 && 'Table'[RegDate] >= last5, "yr5", 'Table'[RegDate] < last5 && 'Table'[RegDate] >= last6, "yr6", 'Table'[RegDate] < last6 && 'Table'[RegDate] >= last7, "yr7", 'Table'[RegDate] < last7 && 'Table'[RegDate] >= last8, "yr8", 'Table'[RegDate] < last8 && 'Table'[RegDate] >= last9, "yr9", 'Table'[RegDate] < last9 && 'Table'[RegDate] >= last10, "yr10" )
3 Replies
- v-frfei-msftCommunity Support
Hi Anonymous ,
To create a calculated column as below.
Type = VAR last1 = EDATE ( TODAY (), -12 ) VAR last2 = EDATE ( TODAY (), -24 ) RETURN IF ( 'Table'[RegDate] <= TODAY () && 'Table'[RegDate] >= last1, "yr1", IF ( 'Table'[RegDate] < last1 && 'Table'[RegDate] >= last2, "yr2", "yr3" ) )Pbix as attached.
- AnonymousNot applicable
Hi thanks for the reply. But if i want to include all the way to >Yr10 how do i do it? sorry very new to power bi.
- v-frfei-msftCommunity Support
Hi Anonymous ,
To update the formula as below:
Type = VAR last1 = EDATE ( TODAY (), -12 ) VAR last2 = EDATE ( TODAY (), -24 ) VAR last3 = EDATE ( TODAY (), -36 ) VAR last4 = EDATE ( TODAY (), -48 ) VAR last5 = EDATE ( TODAY (), -60 ) VAR last6 = EDATE ( TODAY (), -72 ) VAR last7 = EDATE ( TODAY (), -84 ) VAR last8 = EDATE ( TODAY (), -96 ) VAR last9 = EDATE ( TODAY (), -108 ) VAR last10 = EDATE ( TODAY (), -120 ) RETURN SWITCH ( TRUE (), 'Table'[RegDate] <= TODAY () && 'Table'[RegDate] >= last1, "yr1", 'Table'[RegDate] < last1 && 'Table'[RegDate] >= last2, "yr2", 'Table'[RegDate] < last2 && 'Table'[RegDate] >= last3, "yr3", 'Table'[RegDate] < last3 && 'Table'[RegDate] >= last4, "yr4", 'Table'[RegDate] < last4 && 'Table'[RegDate] >= last5, "yr5", 'Table'[RegDate] < last5 && 'Table'[RegDate] >= last6, "yr6", 'Table'[RegDate] < last6 && 'Table'[RegDate] >= last7, "yr7", 'Table'[RegDate] < last7 && 'Table'[RegDate] >= last8, "yr8", 'Table'[RegDate] < last8 && 'Table'[RegDate] >= last9, "yr9", 'Table'[RegDate] < last9 && 'Table'[RegDate] >= last10, "yr10" )