If before 16th of Previous month

[AHowes]

Morning

Trying to set up a custom column / filter (within edit query) that gives a simple yes/no for if date created was before the 16th of the previous month.

i.e: A record created on the 19th of Jan would come back as "no" and 11th Jan would return a "yes"

[v-lili6-msft · Accepted solution]

hi  @AHowes 

In edit queries, you could use this formula to add a custom column as below:

if([Date]< #date(Date.Year(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)),Date.Month(Date.AddMonths(DateTime.Date(DateTime.LocalNow()),-1)),16) ) then "yes" else "no"

Then use this column to filter data.

and here is sample pbix file, please try it.

Regards,

Lin

[amitchandak]

Try in edit query custom column

=Date.Day(Table[Date]) <16

[AHowes]

Hi there, thanks for that but its not quite what I want.

Basically, I want to omit the data from the 17th of the previous month to today.

So... Currently I want to report on everything from the beginning of time to the 16th of Jan

Next month it'll be the beginning of time to the 16th of Feb

Is this possible?

[Anonymous]

See the below solution,

Sample data:

create a calculated column as below,

T/F = IF(

DATE(YEAR('Test Data'[Date Created]),MONTH('Test Data'[Date Created]),1) = DATE(YEAR(TODAY()),MONTH(TODAY())-1,1)

&& DAY('Test Data'[Date Created])<16,"T","F").

Then switch b/w filter options. See the results below,

When True-> gives data prev month before 16th.

result set:

When filter set to false, See the result below,

result set:

If this post helps, then please consider Accepting it as the solution.

[amitchandak]

Try a custom column like this, that will return true/false and you can filter based on that

= Table[Date] <= Date.AddDays(Date.StartOfMonth(Date.AddMonths(Table[Date], -1)),15)

It may be +16

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin