Using the DATEADD expression with an unusual data set

[SharpsmartCP]

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.

[amitchandak]

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

[SharpsmartCP]

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.

[Mohammad_Refaei · 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]) 

[SharpsmartCP]

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.

[Mohammad_Refaei]

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.