Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rabi
Resolver I
Resolver I

Fortnight Dates from a Date Column

Hi everyone, 

Rabi_0-1701300016810.png

 

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()
    )
2 ACCEPTED SOLUTIONS

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

View solution in original post

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

 

View solution in original post

10 REPLIES 10
lbendlin
Super User
Super User

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

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

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

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

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

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.

Rabi_0-1701305282012.png

 

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

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

 

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.