Fortnight Dates from a Date Column

[Rabi]

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

    )

[lbendlin · Accepted solution]

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

[lbendlin]

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

[Rabi]

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

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

[Rabi]

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

[lbendlin]

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

[Rabi]

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.

[lbendlin]

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

[Rabi]

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

[lbendlin · Accepted solution]

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

[Rabi · Accepted solution]

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