cancel
Showing results 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

Resolver I

## Fortnight 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()    )
2 ACCEPTED SOLUTIONS
Super User
``````FortnightStartDate=
VAR w =
INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 )
RETURN
MINX (
FILTER (
TableA,
INT ( WEEKNUM ( [JEDate] + 7, 1 ) / 2 ) = w
),
[JEDate]
)``````
Resolver I

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 10
Super User

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

Resolver I

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

Super User
``````Table =
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]
)
)``````
Resolver I

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

Super User

Please show the expected outcome based on the sample data you provided.

Resolver I

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.

Super User

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

Resolver I

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

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

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors