yes.
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)
What is the value for the “Today” variable?
Not seeing Today in the date table
All I see in the Date Query for Today is
Today = Date.From( DateTime.FixedLocalNow())
Okay, I just run the query and that seems to be working as expected.
This row returns TRUE and that is correct according to the defined logic.
So the only issue could be that the report was refreshed in the Power BI Service before midnight (UTC).
That is so strange. When I refresh in Desktop, it is False for Current Month. Yesterday it picked up previous month dates fine.
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
// fnDateTableYeah, 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!