CODEWITHSUNDEEP
powerbidaxpowerquery
konsama
konsama

Reputation: 347

Is there a way to evaluate for first day of the month and last day of the month?

I wanted to know if there is a way to evaluate dates in a manner that evaluate the 1st of the month and the last of a month

I have two columns 

Start Date  End Date           Result
1/1/2020    1/31/2020          Standard
2/5/2020    2/15/2020          Irregular

Goal intended is to use conditional formatting to highlight dates that do not start on the 1st of a month or end on the last of a month

Tried this solution that was suggested out:

IsStandard = [End Date] = Date.EndOfMonth(Date.FromText([End Date])) and [Start Date] = Date.AddDays(Date.AddMonths(Date.EndOfMonth(Date.FromText([Start Date])),-1),1)

Expression evaluates but returns all results as "False" (NOTE: the dates are as text values so I had to add the additional Date.FromText() )

Is there a way to get the expression to evaluate for the date range from 1st month to last month as true and anything else False??

Upvotes: 0

Views: 1269

Answers (1)

dcbyers
dcbyers

Reputation: 864

PowerQuery version: You could create a custom column as follows:

IsStandard = [End Date] = Date.EndOfMonth([End Date]) and [Start Date] = Date.AddDays(Date.AddMonths(Date.EndOfMonth([Start Date]),-1),1)

This version returns TRUE if the start date is the start of a month and the end date is the end of a month. To suit your use-case, you could invert the logic or break it up into a column to flag is/is not start of month and another to flag is/is not end of month.

The reason for the weird logic around start of month is PowerQuery provides the Date.EndOfMonth function but not a Date.StartOfMonth, so you have to take the end of a month, subtract a month, then add a day to get to the start of month.

Hopefully this helps :) .

Edit RE: always false... This is another quirk of the language and not intuitive, but the key is that the type conversion via Date.FromText() returns a nullable date which is different from a strict Date datatype (this can be confirmed by adding a column based on the formula Date.FromText([End Date]) and examining the data type implicitly assigned to the new column, which is not Date.

Probably the simplest option would be to Change Type on the [Start Date] and [End Date] columns to Date prior to evaluating for 'standard' vs 'irregular' and remove the Date.FromText calls from the new column. This might make the fields more consistent anyway but you may run into an issue if any of the raw values are actually null or empty. If any values are null or empty, you do a check for null to short-circuit the date conversion & check for start/end of month. If the null check fails, you would have the opportunity to then flag the record as something different from 'Standard' and 'Irregular', if that is important for your use-case.

Upvotes: 0

Related Questions