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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SharpsmartCP
Frequent Visitor

Using the DATEADD expression with an unusual data set

Good morning, I have a data set as follows:

1 x Column of dates

1 x Column that is an identifier for a date frequency. eg. 103 represents every third week, 104 represents every forth week, etc. but then there are also codes like 208 representing every 2 months and 209 representing every 3 months.

 

I am able to easily build a new column 'TOADD' that says where 103 is present, this would represent '3' and where 208 is present, this represents '2'.

 

So I then went on to build a formula with DATEADD whereby:

 

NextDate = DATEADD(DateTime[DateKey], 'TOADD', DAY)

 

The issue I have is that I can only use either DAY or MONTH as the interval parameter at the end of the expression. I cannot find a way to vary this based on what is present in my frequency column, or even vary it at all.

 

Any ideas how I would accomplish this? I'm thinking maybe not to start with DAX for this issue and rather do transfroms / calculations in the data model first. Just not sure how to do it!

 

Any help would be appreciated. Thanks.

 

1 ACCEPTED SOLUTION

The challenge here is that DATEADD reutns a date column with the exisiting dates only. As you are using a denormalized structure without a calendar table (you might want to consider this practice) then you may use 2 solutions:

1. Use the simple math like:

 

Next Scheduled Date =
SWITCH (
    [TYPE ADD],
    "DAY", [SCH01_LD1] + [DAYS TO ADD],
    "MONTH",
        [SCH01_LD1] + [DAYS TO ADD] * 30,
    "YEAR",
        [SCH01_LD1] + [DAYS TO ADD] * 365
)

 

 

2. Use Power Query (Preferred):

In Power Query add a new column

if [TYPE ADD] = "DAY" then Date.AddDays([SCH01_LD1],[DAYS TO ADD]) else
if [TYPE ADD] = "WEEK" then Date.AddWeeks([SCH01_LD1],[DAYS TO ADD]) else
if [TYPE ADD] = "MONTH" then Date.AddMonths([SCH01_LD1],[DAYS TO ADD]) else
if [TYPE ADD] = "YEAR" then Date.AddYears([SCH01_LD1],[DAYS TO ADD]) 

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@SharpsmartCP ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Hi there, thanks for coming to my help!

I have uploaded the PBIX to Google Drive.

Here is the link

https://drive.google.com/file/d/17_fQsFfqHEfMhTkKBYIhcpegBR7OWYHG/view?usp=sharing

 

I've added some explanation in the file, so hopefully this will show what I'm trying to achieve with this.

The challenge here is that DATEADD reutns a date column with the exisiting dates only. As you are using a denormalized structure without a calendar table (you might want to consider this practice) then you may use 2 solutions:

1. Use the simple math like:

 

Next Scheduled Date =
SWITCH (
    [TYPE ADD],
    "DAY", [SCH01_LD1] + [DAYS TO ADD],
    "MONTH",
        [SCH01_LD1] + [DAYS TO ADD] * 30,
    "YEAR",
        [SCH01_LD1] + [DAYS TO ADD] * 365
)

 

 

2. Use Power Query (Preferred):

In Power Query add a new column

if [TYPE ADD] = "DAY" then Date.AddDays([SCH01_LD1],[DAYS TO ADD]) else
if [TYPE ADD] = "WEEK" then Date.AddWeeks([SCH01_LD1],[DAYS TO ADD]) else
if [TYPE ADD] = "MONTH" then Date.AddMonths([SCH01_LD1],[DAYS TO ADD]) else
if [TYPE ADD] = "YEAR" then Date.AddYears([SCH01_LD1],[DAYS TO ADD]) 

Great, that worked well for me. Many Thanks. I can see also that for my next steps I will need a dates table, as I now want to summarise occurances for certain dates over the pattern as it repeats and the same date could occur in each column.

Most welcome.... it is a best practice to always have calendar table whenever you work with dates as it facilitates time-intelligence and solidifies the data model.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors