topic Re: Fortnight Dates from a Date Column in DAX Commands and Tips

Fortnight Dates from a Date Column

Rabi — Wed, 29 Nov 2023 23:24:50 GMT

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()

)

Re: Fortnight Dates from a Date Column

lbendlin — Wed, 29 Nov 2023 23:48:03 GMT

What is your definition of a week? Jan 1st 2023 was a sunday.

Re: Fortnight Dates from a Date Column

Rabi — Thu, 30 Nov 2023 00:11:55 GMT

Yeah jan1 2023 was sunday, basically i want group of dates from sunday to sunday exclusive, which is 1/1/2023 to 14/01/2023

Re: Fortnight Dates from a Date Column

lbendlin — Thu, 30 Nov 2023 00:25:43 GMT

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] ) )

Re: Fortnight Dates from a Date Column

Rabi — Thu, 30 Nov 2023 00:35:49 GMT

Thanks Verymuch lbendlin ,

can we instead of creating a new table add this in existing table and for calender date could we use jedate. the starting date for je date is 30/06/2016

Re: Fortnight Dates from a Date Column

lbendlin — Thu, 30 Nov 2023 00:37:58 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Fortnight Dates from a Date Column

Rabi — Thu, 30 Nov 2023 00:49:25 GMT

Apologies lbendlin below is the dummy data, There is 8 million rows of data and je date starts from 30/06/2016. I hope this make it clear.

Re: Fortnight Dates from a Date Column

lbendlin — Thu, 30 Nov 2023 00:58:07 GMT

Describe how this will work at the crossover between years, for example from Dec 1st 2021 to Jan 31st 2022.

Re: Fortnight Dates from a Date Column

Rabi — Thu, 30 Nov 2023 01:00:11 GMT

It it doesnt work between years, can you help me just for 2023

Re: Fortnight Dates from a Date Column

lbendlin — Thu, 30 Nov 2023 01:11:30 GMT

FortnightStartDate= VAR w = INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 ) RETURN MINX ( FILTER ( TableA, INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 ) = w ), [JEDate] )

Re: Fortnight Dates from a Date Column

Rabi — Thu, 30 Nov 2023 01:28:56 GMT

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())