Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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
bourne2000
Helper V
Helper V

How to make fornight calculation in date table?

‎11-08-2022 09:32 PM

Hi

 

I have a date table, and would like to calculate fornight

 

Expected output

 

DateFornight
1/01/2022Jan 1 - 15
2/01/2022Jan 1 - 15
3/01/2022Jan 1 - 15
4/01/2022Jan 1 - 15
5/01/2022Jan 1 - 15
6/01/2022Jan 1 - 15
7/01/2022Jan 1 - 15
8/01/2022Jan 1 - 15
9/01/2022Jan 1 - 15
10/01/2022Jan 1 - 15
11/01/2022Jan 1 - 15
12/01/2022Jan 1 - 15
13/01/2022Jan 1 - 15
14/01/2022Jan 1 - 15
15/01/2022Jan 1 - 15
16/01/2022Jan 16 - 31
17/01/2022Jan 16 - 31
18/01/2022Jan 16 - 31
19/01/2022Jan 16 - 31
20/01/2022Jan 16 - 31
21/01/2022Jan 16 - 31
22/01/2022Jan 16 - 31
23/01/2022Jan 16 - 31
24/01/2022Jan 16 - 31
25/01/2022Jan 16 - 31
26/01/2022Jan 16 - 31
27/01/2022Jan 16 - 31
28/01/2022Jan 16 - 31
29/01/2022Jan 16 - 31
30/01/2022Jan 16 - 31
31/01/2022Jan 16 - 31
1/02/2022Feb 1 - 15
2/02/2022Feb 1 - 15
3/02/2022Feb 1 - 15
4/02/2022Feb 1 - 15
5/02/2022Feb 1 - 15
6/02/2022Feb 1 - 15
7/02/2022Feb 1 - 15
8/02/2022Feb 1 - 15
9/02/2022Feb 1 - 15
10/02/2022Feb 1 - 15
11/02/2022Feb 1 - 15
12/02/2022Feb 1 - 15
13/02/2022Feb 1 - 15
14/02/2022Feb 1 - 15
15/02/2022Feb 1 - 15
16/02/2022Feb 2 - 28
17/02/2022Feb 2 - 28
18/02/2022Feb 2 - 28
19/02/2022Feb 2 - 28
20/02/2022Feb 2 - 28
21/02/2022Feb 2 - 28
22/02/2022Feb 2 - 28
23/02/2022Feb 2 - 28
24/02/2022Feb 2 - 28
25/02/2022Feb 2 - 28
26/02/2022Feb 2 - 28
27/02/2022Feb 2 - 28
28/02/2022Feb 2 - 28
1/03/2022Mar 1 - 15

 

I have day number in my date table and used below dax to create a new column 

 

 

fortnight = IF( Date[DayNo] <= 15, "Fortnight-1", "Fornight-2")

 

 

The above dax works to seperate the fornights. However, I am not able to get the above output like Jan 1-15, Jan 16-31 etc

 

Can anyone advise how to do this? I created date table using M query

 

 

Labels:
Message 1 of 3
448 Views
0
1 ACCEPTED SOLUTION
kunal15sep
Frequent Visitor

‎11-08-2022 10:50 PM
fortnight =
var lastdayinMonth= ENDOFMONTH('Date'[Date])


return
if(DAY([Date])<=15, 'Date'[Month] & " 1-15",'Date'[Month]& " 16-" & day(lastdayinMonth))

View solution in original post

Message 2 of 3
409 Views
2 REPLIES 2
kunal15sep
Frequent Visitor

‎11-08-2022 10:51 PM
Using "Month" as a sepearate column FORMAT([Date],"mmm")
Message 3 of 3
408 Views
kunal15sep
Frequent Visitor

‎11-08-2022 10:50 PM
fortnight =
var lastdayinMonth= ENDOFMONTH('Date'[Date])


return
if(DAY([Date])<=15, 'Date'[Month] & " 1-15",'Date'[Month]& " 16-" & day(lastdayinMonth))
Message 2 of 3
410 Views

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

View All