Forum Discussion
rmcneish
Helper I
Helper IHow to create a custom week number by date'
Dear super users. I need to create a column with week number according to a date range since "START" to "END". TY for your support. Regards. Vvelarde disculpa por etiquetarte pero en el...
I got it !!
#week = var datestart = MAXX(FILTER(TEMPORADAS,TEMPORADAS[INICIO]<='E+R+N'[F. PROD]&&TEMPORADAS[FIN]>'E+R+N'[F. PROD]),TEMPORADAS[INICIO]) return DATEDIFF(datestart,'E+R+N'[F. PROD],WEEK)+1thanks.
rmcneishHelper I
Helper IDear Seward12533, thanks you for helping me, but I have some problems.
Well, here is my detailed tables, and I hope you help me.
Seward12533
Solution Sage
Solution SageLooks like your using this as a measure the example I geve you was for a calculate column. If so you need to use an aggregate value you can't just reference and entire column. Try
DATEDIFF(MIN('E+R+N'[F. PROD]),RefStartDate,WEEK)
However I still recommend a Calendar Table based approach its much more flexible.
See the attached PBIX file - https://1drv.ms/u/s!AuCIkLeqFmlhhJhuwkc_FWIXnAPPvw
I created a Seasons table
| Season | Start | End |
| 2014 - 1 | 4/1/2014 | 6/1/2014 |
| 2014 - 2 | 6/2/2014 | 12/31/2014 |
| 2015 - 1 | 1/1/2015 | 6/4/2015 |
| 2015 - 2 | 6/5/2015 | 1/24/2016 |
| 2016 - 1 | 1/25/2016 | 5/30/2016 |
| 2016 - 2 | 5/31/2016 | 1/26/2017 |
| 2017 - 1 | 1/27/2017 | 6/1/2017 |
| 2017 - 2 | 11/11/2017 | 1/28/2018 |
| 2018 - 1 | 1/29/2018 | 5/30/2018 |
| 2018 - 2 | 7/1/2018 | 1/31/2019 |
Then using DAX I created a Calendar Table that includes Seasons and Season Week along with Calendar Week.
DIMDATE =
ADDCOLUMNS (
CALENDAR ( DATE ( 2014, 1, 1 ), DATE ( 2020, 12, 1 ) ),
"Calendar Week", FORMAT ( [Date], "WW" ),
"Season",
VAR DateCheck = [Date]
VAR RefStartDate = CALCULATE ( MIN ( SEASONS[Start] ), SEASONS[End] >= DateCheck )
RETURN
LOOKUPVALUE ( SEASONS[Season], SEASONS[Start], RefStartDate ),
"Season Week",
VAR DateCheck = [Date]
VAR RefStartDate = CALCULATE ( MIN ( SEASONS[Start] ), SEASONS[End] >= DateCheck )
RETURN
VAR Result = DATEDIFF(RefStartDate,[Date],WEEK)+1 RETURN
IF(Result>0,Result)
)