Increment column every 1st and 15th of the month

[Whoule]

Hi,

I have created a date table and now I want a column that returns a number incremented by 1 every 1st and 15th of the month.

Here is an example:

2023-12-12	1
2023-12-13	1
2023-12-14	1
2023-12-15	2
2023-12-16	2
2023-12-17	2

Any idea on how to achieve this? Thanks !

[Ahmedx]

Sorry, I found a mistake, now everything is correct

[Whoule]

Hi @Ahmedx,

This doesn't work as some months have 31 days so after one month dividing by 15 doesnt work anymore.

[Ahmedx]

pls try again

[Whoule]

Still doesn't work. The ID needs to change not every 15 days but only on the 1st and 15th of each month.

[Ahmedx]

where there are 31 days or 29 what to do

[Greg_Deckler · Accepted solution]

@Whoule Try this:

Column = 
  VAR __Date = [Date]
  VAR __Day = DAY( [Date] )
  VAR __Table = FILTER( 'Table', [Date] <= __Date && ( DAY( [Date] ) = 1 || DAY( [Date] = 15 ) )
  VAR __Result = COUNTROWS( __Table )
RETURN
  __Result