Forum Discussion
Power query If submit date between start date and end date, then "on time" else "late"
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
Anonymous
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"
Super UserHey,
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
TomMartens Ah right, thanks for the clarification. I'm completely new to Power BI, and it's really been doing my head in.