Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
15 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |