Forum Discussion
Using the DATEADD expression with an unusual data set
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])
Super UserSharpsmartCP ,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])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.
