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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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