Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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 ,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.
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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |