none
Syntax for comparing dates in an IF/ELSE statement RRS feed

  • Question

  • Hello,

    My purpose is to create a column which analyzes 2 columns, start date (ServiceDate) and end date (Line_Item_End_Date__c) and displays how many days, if any, land in August (I'm using this to establish revenue for billing).

    I've successfully created a DAX code which does this, which looks like this:

    (if(and('Opportunity Product'[Line_Item_End_Date__c]>date(2016,7,31),'Opportunity Product'[ServiceDate]<date(2016,9,1)),if(and('Opportunity Product'[ServiceDate]<=date(2016,8,1),'Opportunity Product'[Line_Item_End_Date__c]>=date(2016,8,31)),31,if('Opportunity Product'[ServiceDate]<=date(2016,8,1),DATEDIFF(date(2016,8,1),'Opportunity Product'[Line_Item_End_Date__c],DAY)+1,if('Opportunity Product'[Line_Item_End_Date__c]<=date(2016,8,31),datediff('Opportunity Product'[ServiceDate],'Opportunity Product'[Line_Item_End_Date__c],DAY)+1,datediff('Opportunity Product'[ServiceDate],date(2016,8,31),DAY)+1))),0))

    It's messy, but it works.

    I'm trying to rewrite the same thing in PowerQuery and so far I have this:

    if Value.Compare([Line_Item_End_Date__c],DateTime.FromText("2016-08-01"),0)=-1 or Value.Compare([ServiceDate],DateTime.FromText("2016-08-31"),0)=1
    then 0
    else if Value.Compare([ServiceDate],DateTime.FromText("2016-7-31"),0)=-1 and Value.Compare([Line_Item_End_Date__c],DateTime.FromText("2016-09-01"),0)=1
    then 31
    else if Value.Compare([ServiceDate],DateTime.FromText("2016-07-31"),0)=-1
    then Duration.Days(Duration.From(DateTime.FromText("2016-08-01")-[Line_Item_End_Date__c]))+1
    else if Value.Compare([Line_Item_End_Date__c],DateTime.FromText("2016-09-01"),0)=-1
    then Duration.Days(Duration.From([ServiceDate]-[Line_Item_End_Date__c]))+1
    else 
    Duration.Days(Duration.From([ServiceDate]-DateTime.FromText("2016-08-31"))+1)

    Two questions:

    - is Value.Compare the correct function to use here? I'm new to PQ and when I tried using a > operator I got an error that it cannot be used on Dates.

    - Is the above is correct, then what else is wrong? The issue I'm having is that everything is evaluating to 0 as per the first condition, whether or not the End Date is before or after Aug. 1st.

    PS ServiceDate and Line_Item_End_Date__c are "Date" type columns in December-14-16 type format.

    Thank you in advance :)

    Thursday, August 4, 2016 9:37 PM

Answers

  • The reason for the error-message is that you aim to compare Date with DateTime-values. They are different and not "compatible". Have a look at the link provided above and check out the huge offer of different Date-functions in Power Query.

    Once you compare date/times of the same type, it's as easy as in Excel:

    You can subtract in order to get durations:

    [Date]-#date(2016, 08,05)

    or compare with TRUE / FALSE returned:

    [Date] < Date.FromText("2016, 08,05")


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Lina Blank Friday, August 5, 2016 6:22 PM
    Friday, August 5, 2016 4:55 AM
    Moderator

All replies

  • Hi Lina,

    You might consider leveraging other date functions like Date.Day() and Date.Month() to help simplify the code.

    https://msdn.microsoft.com/en-us/library/mt296606.aspx

    Many thanks.

    Thursday, August 4, 2016 11:53 PM
  • The reason for the error-message is that you aim to compare Date with DateTime-values. They are different and not "compatible". Have a look at the link provided above and check out the huge offer of different Date-functions in Power Query.

    Once you compare date/times of the same type, it's as easy as in Excel:

    You can subtract in order to get durations:

    [Date]-#date(2016, 08,05)

    or compare with TRUE / FALSE returned:

    [Date] < Date.FromText("2016, 08,05")


    Imke Feldmann TheBIccountant.com

    • Marked as answer by Lina Blank Friday, August 5, 2016 6:22 PM
    Friday, August 5, 2016 4:55 AM
    Moderator
  • Awesome, this helped a lot! I didn't realise Date and Date.Time would be different formats :)

    Thanks so much!

    Friday, August 5, 2016 6:22 PM