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.
I'm simply trying to get a date value calculated using a column depending on a condition, but it doesnt' work, what I am missing?
Calculated Due Date: If the contract is digital then use the (enddate column + 7 days) otherwise use (receiveddate column + 7)
"Table1"
Received Date EndDate Format Due Date
01/01/2000 01/07/2000 Digital ???????
(FORMULA THAT IS NOT RETU?rn)
DueDate = IF(Table1[Format]="Digital",DATEADD(Table1[EndDate],7,DAY),DATEADD(Table1[ReceivedDate],7,DAY))
Thanks in advance
Solved! Go to Solution.
Hi @VFXPro
If you are using the Power Query Editor, then you can add a custom column using this formula. For something like this it doesn't really matter if you create it in M (Power Query) or DAX (Power BI Desktop)
if [Format] = "Digital" then Date.AddDays([ReceivedDate],7) else Date.AddDays([EndDate],7)
DATEADD is an unusual function. If the date you end up with doesn't exist in the table already, it shows as blank.
I've never looked in detail at this but I presume it uses the in-built pseudo dates (hidden) table, and if it can't find it, it shows blank.
2 solutions: 1 - just add (+) the number (7 in your case) to the date
or 2 - use DATEADD but develop your own 'dates' table in the model
Hi @VFXPro
Give this a go, I think it's close. I have attached a PBIX file
DueDate = IF( Table1[Format]="Digital", Table1[EndDate]+7, Table1[ReceivedDate]+7 )
Thanks Phil!! Your file works fine. It look like you are creating this new calculated column directly from the table editor, where I was using the Query editor (via Edit Query). When I do use the query editor, with your formula (or mine) I get the followin error...
Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.
Is there a guidelines that tells the user when to create a new column in the data tab vs the use the "Edit Query" that opens a separate editor?
Hi @VFXPro
If you are using the Power Query Editor, then you can add a custom column using this formula. For something like this it doesn't really matter if you create it in M (Power Query) or DAX (Power BI Desktop)
if [Format] = "Digital" then Date.AddDays([ReceivedDate],7) else Date.AddDays([EndDate],7)
Thanks @Phil_Seamark
This is very valuable guidance... Thank you very much for your kindness and sharing your knowledge.
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |