Calculated Column between dates

[StefanM]

Hello everyone!

I am trying to add a new column which will simply number a range of pre-existing dates. Either a date table, or the data table - though my preference is for a new column created with Dax code added to my date table.

The goal is for the range to always begin on the 16th of a month and end on the 15th of the next month.

I want Dec 16th - Jan 15 to be #1
Jan 16th - Feb 15th to be #2
All the way up to Nov 16th - Dec 15th being #12

I have had success with comparing the difference bewteen flat months.. but this query is really just boggling my mind atm !

Any wizards out there that can offer some assistance?

[v-yulgu-msft · Accepted solution]

Hi @StefanM,

Please refer to below DAX formula:

date range =
IF (
    [Date].[Day] >= 16
        && [Date].[MonthNo] = 12,
    1,
    IF ( [Date].[Day] >= 16 && [Date].[MonthNo] < 12, [Date].[MonthNo] + 1, [Date].[MonthNo] )
)

Best regards,

Yuliana Gu

[StefanM]

Oh wow, thanks guys! I thought I had responded to @Greg_Deckler as I was leaving work on Monday, but the post didnt send due to some HTML formatting (copied the table from google sheets :P) I will leave it below for posterity sake though : D

@v-yulgu-msftthat works perfectly! And looking at it, I can see why. Thank you very much! I should be able to work out the rest of the stuff I need from this.

Thanks again!

---

 
Hi Greg! Thanks for reaching out.

Yep, thats pretty much spot on. This is generally what I am looking for as an end result:

Existing in table	To be Created	To be Created
16/11/2018	12: Nov - Dec	12: Week 1
17/11/2018	12: Nov - Dec	12: Week 1
18/11/2018	12: Nov - Dec	12: Week 1
19/11/2018	12: Nov - Dec	12: Week 1
20/11/2018	12: Nov - Dec	12: Week 1
21/11/2018	12: Nov - Dec	12: Week 1
22/11/2018	12: Nov - Dec	12: Week 1
23/11/2018	12: Nov - Dec	12: Week 2
24/11/2018	12: Nov - Dec	12: Week 2
25/11/2018	12: Nov - Dec	12: Week 2
26/11/2018	12: Nov - Dec	12: Week 2
27/11/2018	12: Nov - Dec	12: Week 2
28/11/2018	12: Nov - Dec	12: Week 2
29/11/2018	12: Nov - Dec	12: Week 2
30/11/2018	12: Nov - Dec	12: Week 3
1/12/2018	12: Nov - Dec	12: Week 3
2/12/2018	12: Nov - Dec	12: Week 3
3/12/2018	12: Nov - Dec	12: Week 3
4/12/2018	12: Nov - Dec	12: Week 3
5/12/2018	12: Nov - Dec	12: Week 3
6/12/2018	12: Nov - Dec	12: Week 3
7/12/2018	12: Nov - Dec	12: Week 4
8/12/2018	12: Nov - Dec	12: Week 4
9/12/2018	12: Nov - Dec	12: Week 4
10/12/2018	12: Nov - Dec	12: Week 4
11/12/2018	12: Nov - Dec	12: Week 4
12/12/2018	12: Nov - Dec	12: Week 4
13/12/2018	12: Nov - Dec	12: Week 4
14/12/2018	12: Nov - Dec	12: Week 5
15/12/2018	12: Nov - Dec	12: Week 5
16/12/2018	12: Nov - Dec	12: Week 5
17/12/2018	1: Dec - Jan	1: Week 1

I tried a bunch of time based things, but none worked for me 😕

[ryan_mayu]

@StefanM

I used your sample data and created two test columns for you. Please try this.

test1 =

VAR monthnumber=if(DAY('Sheet4'[Existing in table])>=16&&MONTH('Sheet4'[Existing in table])=12,1,

if(DAY('Sheet4'[Existing in table])<16&&MONTH('Sheet4'[Existing in table])=1,1,

if(DAY('Sheet4'[Existing in table])>=16,MONTH('Sheet4'[Existing in table])+1,MONTH('Sheet4'[Existing in table]))))

VAR MonthName = if(

                    DAY('Sheet4'[Existing in table])>=16&&MONTH('Sheet4'[Existing in table])=12,

                    "Dec-Jan",

                    if(

                        DAY('Sheet4'[Existing in table])<16&&MONTH('Sheet4'[Existing in table])=1,

                        "Dec-Jan",

                        if(

                            DAY('Sheet4'[Existing in table])>=16,

                            CALCULATE(SELECTEDVALUE(date2[Month]),FILTER('date2',MONTH('Sheet4'[Existing in table])='date2'[Monthnumber]))&"-"&                                                             CALCULATE(SELECTEDVALUE(date2[Month]),FILTER('date2',MONTH('Sheet4'[Existing in table])='date2'[Monthnumber]-1)),

                            CALCULATE(SELECTEDVALUE(date2[Month]),FILTER('date2',MONTH('Sheet4'[Existing in table])='date2'[Monthnumber]+1))&"-"&                                                             CALCULATE(SELECTEDVALUE(date2[Month]),FILTER('date2',MONTH('Sheet4'[Existing in table])='date2'[Monthnumber]))

)))

return monthnumber&":"&MonthName

test2 =

VAR monthnumber=if(DAY('Sheet4'[Existing in table])>=16&&MONTH('Sheet4'[Existing in table])=12,1,

if(DAY('Sheet4'[Existing in table])<16&&MONTH('Sheet4'[Existing in table])=1,1,

if(DAY('Sheet4'[Existing in table])>=16,MONTH('Sheet4'[Existing in table])+1,MONTH('Sheet4'[Existing in table]))))

VAR week = ROUNDUP(DIVIDE(DATEDIFF(DATE(2018,11,15),'Sheet4'[Existing in table],DAY),7),0)

return monthnumber&":Week "&week

Meanwhile, you need to create another table which is like below screenshot.

please see my test results below.Hope this is helpful.

[StefanM]

Wow @ryan_mayu, I must have missed your message with the end of year turnover but that is some good work! I have managed to incorporate it into my data set quite nicely, tyvm!

Test 1 is working as expected. However, I am having some issues with Test 2.

The dataset works over many years, going back to 2017. What I am seeing atm is something like this:

I have to admit, I actually dont have any understanding on the Week variable you used. I could follow along with month number, oh man, I am just not advanced enough to follow along with week. I didnt even know roundup was a thing! ^^ 

Is there perhaps something wrong with the way I have implemented your code?

[ryan_mayu]

@StefanM

I didn't see your example clearly. I thought you want the continous week number. If you want the result the same as your example. The logic is a little complicated.

Please try the below one. Hope this is helpful. Thanks

test2 = 
VAR MON=MONTH(EDATE('Sheet28'[date],-1))
VAR YR=YEAR(EDATE('Sheet28'[date],-1))
VAR monthnumber=if(DAY('Sheet28'[date])>=16&&MONTH('Sheet28'[date])=12,1,
if(DAY('Sheet28'[date])<16&&MONTH('Sheet28'[date])=1,1,
if(DAY('Sheet28'[date])>=16,MONTH('Sheet28'[date])+1,MONTH('Sheet28'[date]))))

VAR WEEKNUMBER= if(DAY('Sheet28'[date])>15,ROUNDUP(DIVIDE(DAY('Sheet28'[date])-15,7),0),ROUNDUP(DIVIDE((day('Sheet28'[date])+Mod(CALCULATE(COUNT('Sheet28'[date]),FILTER('Sheet28',MONTH('Sheet28'[date])=MON&&YEAR('Sheet28'[date])=YR&&DAY('Sheet28'[date])>15)),7)),7),0)+2)
RETURN monthnumber&":Week "&WEEKNUMBER

[Greg_Deckler]

So, you have a date table and you want each day assigned to your group, is that correct?