cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
StefanM Regular Visitor
Regular Visitor

Calculated Column between dates

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Calculated Column between dates

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Calculated Column between dates

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

v-yulgu-msft Super Contributor
Super Contributor

Re: Calculated Column between dates

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

StefanM Regular Visitor
Regular Visitor

Re: Calculated Column between dates

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 tableTo be CreatedTo be Created
16/11/201812: Nov - Dec12: Week 1
17/11/201812: Nov - Dec12: Week 1
18/11/201812: Nov - Dec12: Week 1
19/11/201812: Nov - Dec12: Week 1
20/11/201812: Nov - Dec12: Week 1
21/11/201812: Nov - Dec12: Week 1
22/11/201812: Nov - Dec12: Week 1
23/11/201812: Nov - Dec12: Week 2
24/11/201812: Nov - Dec12: Week 2
25/11/201812: Nov - Dec12: Week 2
26/11/201812: Nov - Dec12: Week 2
27/11/201812: Nov - Dec12: Week 2
28/11/201812: Nov - Dec12: Week 2
29/11/201812: Nov - Dec12: Week 2
30/11/201812: Nov - Dec12: Week 3
1/12/201812: Nov - Dec12: Week 3
2/12/201812: Nov - Dec12: Week 3
3/12/201812: Nov - Dec12: Week 3
4/12/201812: Nov - Dec12: Week 3
5/12/201812: Nov - Dec12: Week 3
6/12/201812: Nov - Dec12: Week 3
7/12/201812: Nov - Dec12: Week 4
8/12/201812: Nov - Dec12: Week 4
9/12/201812: Nov - Dec12: Week 4
10/12/201812: Nov - Dec12: Week 4
11/12/201812: Nov - Dec12: Week 4
12/12/201812: Nov - Dec12: Week 4
13/12/201812: Nov - Dec12: Week 4
14/12/201812: Nov - Dec12: Week 5
15/12/201812: Nov - Dec12: Week 5
16/12/201812: Nov - Dec12: Week 5
17/12/20181: Dec - Jan1: Week 1


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

ryan_mayu Established Member
Established Member

Re: Calculated Column between dates

@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.
screenshot.JPG
 
please see my test results below.Hope this is helpful.
 
screenshot1.JPG
 
 
StefanM Regular Visitor
Regular Visitor

Re: Calculated Column between dates

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:

Test 1 & 2.png

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 Established Member
Established Member

Re: Calculated Column between dates

@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

c1.JPG

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 183 members 2,134 guests
Please welcome our newest community members: