Need help on Calculation with Calculated columns

[Anonymous]

Hi All,

I need your support in resolving the logic and syntax please:

Logic: The calculated RTdate is 3aDate minus 4 weeks (omit any records with 3aDate blank)
I have created a column in power query:
Syntax used: RTDate=Date.AddDays([3aDate] ,-28) , but not sure how to omit that blank record

*********************

With the cretaed RTDate column, i have created column (RStatus) in data tab with the below logic for a,b &c

Logic:

a.Overdue --->if actual_date is blank and the current date is later than the RTdate
b. 4Due --->if actual_date is blank and the current date is later than 4 weeks before RTdate
c. onTime---> if actual_date is on or before RTdate

Syntax used:
RStatus =if([actual_date]=BLANK() && NOW()>[RTdate],"Overdue",
if([actual_date]=BLANK() && NOW()>DATEADD([RTdate],-28,DAY),"4Due",
if([actual_date]=TODAY() || ([actual_date]<([RTdate])) ,"onTime", "")))

When i used the above column(RStatus) to pull in visual and could notice there was issue with the logic.
Your help is much appreciated!!!

[v-jingzhang]

Hi @Anonymous 

DATEADD function expects a date column as the first parameter rather than a date value. You can add integers (positive and negative) to a date directly to get a date that many days after or before the date. Try DAX

RStatus =
IF (
    ISBLANK ( [actual_date] ),
    IF ( NOW () > [RTdate], "Overdue", IF ( NOW () > [RTdate] - 28, "4Due" ) ),
    IF ( [actual_date] <= [RTdate], "onTime", "" )
)

And when you created a column in power query, what do you mean to omit that blank record? When a date value is null, Date.AddDays will return null too.

Let me know if you still have questions. Sample data is appreciated to help make the expected result easier to understand.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Greg_Deckler]

@Anonymous I would recommend converting to a SWITCH(TRUE(),...) statement as this tends ot make the logic much more obvious and easily handled.