cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bipotato Frequent Visitor
Frequent Visitor

Power query If submit date between start date and end date, then "on time" else "late"

Hi,

 

I was wondering how you would input a string of text into a new column if submission date is between two dates (or greater than start date and smaller than end date). 

submitted.JPG

 

I've heard about datesbetween, but the syntax seems to mention CALCULATE, and I'm not calculating anything, just trying to filter for if the submit date is late or not. (Visual date slicers would be too tedious as well).

 

Thanks!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: DAX code If submit date between start date and end date, then "on time" else "lat

Hey,

 

please be aware that the Submitted On datetime is greater than the End date.

 

Where 3/03/2019 10:27:55 PM is close to the 4th of March

the end date 3/03/2019 12:00:00 AM marks midnight between the 2nd and 3rd of march.

 

If the time is not relevant you might consider to extract the date part from all of your columns or just add 1 day to the End column.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
Super User
Super User

Re: DAX code If submit date between start date and end date, then "on time" else "lat

@bipotato 

 

As @TomMartens  also suggested, in your case you can do the calculation using date part only

 

so as a custom column try

 

=if Date.From([Submitted On]) >= Date.From([Start]) and Date.From([Submitted On]) <= Date.From([End]) then "on time" else "late"
9 REPLIES 9
Super User
Super User

Re: DAX code If submit date between start date and end date, then "on time" else "lat

@bipotato 

 

Just make this small amendment to your formula

 

=if [submitted on] >[Start] and [submitted on] <[End] 

bipotato Frequent Visitor
Frequent Visitor

Re: DAX code If submit date between start date and end date, then "on time" else "lat

@Zubair_Muhammad  Thanks for the quick reply! I've tried that, and "late" and "on time" do appear - except not in the right way. I know for a fact that in this set of data, no one submitted it late:

late or on time.JPG

Super User
Super User

Re: If submit date between start date and end date, then "on time" else "late"

Hey,

 

from the screenshot you posted it's clear that you are looking for a solution in M meaning Power Query.

 

The formula has to look similar to this:

if [Datum] >= [ShiftStart] and [Datum] <= [ShiftEind] then "this" else "that"

Just use the column reference [Submitted On] after the and.

 

The functions DATESBETWEEN and CALCULATE are DAX functions, even if both Power Query / M and DAX are able to create a calculated column that can be used for filtering in the final data model, they are different and both have their own subtleties. In this specific case it does not matter if you use Power Query / M or DAX.

 

Regards,
Tom

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: DAX code If submit date between start date and end date, then "on time" else "lat

@bipotato 

 

I am little confused.

The yellow highlighted "Submitted on" figures in your picture above appear to be between Start and End dates..So the formula should return late. Isn't it?

bipotato Frequent Visitor
Frequent Visitor

Re: If submit date between start date and end date, then "on time" else "late"

@TomMartens Ah right, thanks for the clarification. I'm completely new to Power BI, and it's really been doing my head in.

bipotato Frequent Visitor
Frequent Visitor

Re: DAX code If submit date between start date and end date, then "on time" else "lat

@Zubair_Muhammad My apologies, I accidentally switched the two - but I do have another follow-up question about inclusivity - adding the equal signs to make it "greater / less than or equal to" doesn't seem to include submissions as on time if they submitted on the last day of the date range?

late or on time.JPG

Super User
Super User

Re: DAX code If submit date between start date and end date, then "on time" else "lat

Hey,

 

please be aware that the Submitted On datetime is greater than the End date.

 

Where 3/03/2019 10:27:55 PM is close to the 4th of March

the end date 3/03/2019 12:00:00 AM marks midnight between the 2nd and 3rd of march.

 

If the time is not relevant you might consider to extract the date part from all of your columns or just add 1 day to the End column.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Highlighted
Super User
Super User

Re: DAX code If submit date between start date and end date, then "on time" else "lat

@bipotato 

 

As @TomMartens  also suggested, in your case you can do the calculation using date part only

 

so as a custom column try

 

=if Date.From([Submitted On]) >= Date.From([Start]) and Date.From([Submitted On]) <= Date.From([End]) then "on time" else "late"
bipotato Frequent Visitor
Frequent Visitor

Re: DAX code If submit date between start date and end date, then "on time" else "lat

Thanks for your help guys! Works perfectly. 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 47 members 1,185 guests
Please welcome our newest community members: