Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StefanM
Helper II
Helper II

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
v-yulgu-msft
Employee
Employee

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
v-yulgu-msft
Employee
Employee

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.

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 😕

@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
 
 




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

Proud to be a Super User!




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?

@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

 





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

Proud to be a Super User!




Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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