cancel
Showing results for
Did you mean:

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

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:

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
Solution Specialist

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 (
"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
5 REPLIES 5
Super User

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

Frequent Visitor

Hi there, thanks for coming to my help!

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

Solution Specialist

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 (
"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
Frequent Visitor

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.

Solution Specialist

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.