DAX expression to filter dates verses Query Editor date filter

If that is still hapening now, when you refresh that query in Desktop, only one thing left to try, turn your Date table function query into a table query - so you can see the value for the variable Today

Steps illustrating that process can be found here.

or here if you’d rather see a video…

I’ll try that.

When I hover over ‘Today’ in the Applied Steps, it has a text box that picks up the comment Date table starts here. I don’t see a value. Have to leave for a few but will get back on in about 90 minutes.

//let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let

  // Declare your variables here
  StartDate = #date( 2020, 9, 1),
  EndDate = #date( Date.Year (Today) +1, 8, 31),
  FYStartMonth = null,

  // Date table code starts here

    Today = Date.From( DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),   
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear])),
    AddCurrentDates = Table.AddColumn( AddFY, "Current Month Filter",  each (if Date.Day(Today)=1 then Date.IsInPreviousMonth([Date]) else Date.IsInCurrentMonth([Date]) and [Date] < Today ), type logical)
in
    AddCurrentDates
//in
//    fnDateTable

Yeah, you need to select that Step in the Applied Steps pane to see the result in the Preview Pane and should return June 2, 2021 If not, you should check your system’s datetime settings…

Shows correctly

Still shows False for the Current Month Filter

What does?

Have you looked into your system datetime settings? Because DateTime.FixedLocalNow returns a datetime value set to the current date and time on the system.

I was able to see 6/2/2021 as the variable value for Today. Trying to figure out how to locate system datetime settings now

My current date and time are showing correctly in the system settings.

This has got me puzzled… your Today variabe is returning the correct value and when I copied your table query from post #23 that also returned the correct result (for me al least…)

Can you make a screen grab with the results and formula bar AFTER you’ve set the filter on [Current month Filter] = true?

I found that the Current Month Filter only shows True for Yesterday’s date. Can the M code be modified to set True for all of the days in the current month?

DEMO for current month.pbix (39.4 KB)

Sure, slight modification - see below.

AddCurrentDates = Table.AddColumn( AddFY, "Current Month Filter", each (if Date.Day(Today)=1 then Date.IsInPreviousMonth([Date]) else Date.IsInCurrentMonth([Date]) and [Date] <= Today ), type logical)

That’s actually correct. I only want the dates in the current month prior to today’s date. I looked at the table in the model that the date table joins to and lo and behold, it only goes to 5/31/21. Once I brought over a value to the table, it was evident.
Current Month Filter.docx (16.3 KB)

I went ahead and marked the solution because that exercise taught me how to find the Today variable and that helped a lot. Thank you for the time you spent with me. As it turns out, the Current Date Filter is working as expected. My problem is getting current data from a different query so this problem is solved. Thanks Melissa!