Forum Discussion
Rabi
Resolver I
Resolver IFortnight Dates from a Date Column
Hi everyone,
I managed to get Group of weekly dates form jedate column, But i cant get it to group by fortnight, the result would be 1/1/2023 repeating 14 times and the next date repeating 14 times would be 15/01/2023. here is the dax i used to get thhe weekly dates. Please help me to slove this.
FortnightStartDate = VAR StartDate = 'tableA'[JEDate] - MOD(WEEKDAY('tableA'[JEDate], 1) - 1, 14) RETURN IF( INT(DATEDIFF(StartDate, 'tableA'[JEDate], DAY) / 14) < 1, StartDate + (INT(DATEDIFF(StartDate, 'tableA'[JEDate], DAY) / 14) * 14), BLANK() ) |
FortnightStartDate= VAR w = INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 ) RETURN MINX ( FILTER ( TableA, INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 ) = w ), [JEDate] )Legend, Thanks very much for your time lbendlin, I appreciate your help. There was slight issue with the dax,
FortnightStartDate= VAR w = INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 ) var x= MINX ( FILTER ( TableA, INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 ) = w && [jedate].[year]=2023 ), [JEDate] ) return, if([jedate].[year]=2023,x,blank())
10 Replies
- lbendlin
Super User
What is your definition of a week? Jan 1st 2023 was a sunday.
RabiYeah jan1 2023 was sunday, basically i want group of dates from sunday to sunday exclusive, which is 1/1/2023 to 14/01/2023
- lbendlin
Table = ADDCOLUMNS ( CALENDAR ( "2023-01-01", "2023-12-31" ), "FortnightStartDate", VAR w = INT ( WEEKNUM ( [Date] + 7, 1 ) / 2 ) RETURN MINX ( FILTER ( CALENDAR ( "2023-01-01", "2023-12-31" ), INT ( WEEKNUM ( [Date] + 7, 1 ) / 2 ) = w ), [Date] ) )