Forum Discussion
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.
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])
5 Replies
- amitchandak
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.
- Mohammad_Refaei
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 ( [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])
