End of Month not working correctly

Hitman · May 30, 2020, 4:59pm

Scenario

Report has 3 different date filters directly from date table (3 filters includes: any date in last seven days, any date ever, end of month)

Issue

When i select the “end of month” filter it shows just one date for the end of the month (correct)
However, this should also filter the “any date” filter to also only show the end of month (EoM) date but instead any date within that month can be selected (incorrect)

As the “EoM” filter impacts the “any date” filter to only show the dates in the current month this does not appear to be an edit interaction issue. I wondered if this could be anything wrong with the M Language which is included at the bottom on this post.

This issue is also duplicated when i drag each date filter into a table. If i drag in “EoM” to a table it will show just one date for that day. If I also drag in “any day” filter it will show each date for the entire month selected by EoM.

Date table created off a M Language query and below is the line relating to the end of the month:

#“Inserted End of Month” = Table.AddColumn(#“Inserted Start of Month”, “End of Month”, each Date.EndOfMonth([Date]), type date),

I also have a measure to show which date has been selected on the report. This however is not working which i presume is due to the above issue as it does not know what date to select.

Looking forward to see if anyone is able to answer… Thanks All!

Melissa · May 30, 2020, 5:25pm

Hi @Hitman,

Welcome to the Forum.

Without a model I’ll make some presumptions…

The “any date” filter is based on the Dates[Date] from the date table
The “end of month” filter is based on the Dates[End of Month] from the date table.

If you examine the date table and place these columns side by side, you will notice that the End of Month date is repeated for each day in the Month and this is correct. However your requirement is different, so if you add an additional column to your Date table, for example:

InsertIsEndOfMonth = Table.AddColumn(InsertIsHoliday, “IsEndOfMonth”, each if [Date] = [End of Month] then true else false, type logical)

Instead of true/false you could also return the Date value OR create similar logic using DAX, there are a lot of solutions possible. Whatever you choose you can add or modify the filtering, making sure a single date is visible to the filter.

I hope this is helpful.

Hitman · May 30, 2020, 6:14pm

Melissa, This was a very clear and concise answer. Genuine appreciation heading your way (Thanks)

One quick question though. I pasted the line into the middle of my M query date table (below) and i got a few errors. I had a little play but to no avail - This is no doubt due to my lack of understanding with M Language but any suggestions please?

Ps Can you think of a better way to spend Saturday Eves than doing Power Bi - We Rock!

Date Table :

let
// configurations start
FromYear = 2018, // set the start year of the date dimension. dates start from 1st of January of this year
ToYear=2021, // set the end year of the date dimension. dates end at 31st of December of this year

//ToDate = DateTime.Date(Date.EndOfYear(Date.AddYears(DateTime.LocalNow(),1)),
//dynamic to date

StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
firstDayofWeek=Day.Monday, // set the week’s start day, values: Day.Monday, Day, Sunday…
// configuration end
FromDate=#date(FromYear,1,1),
ToDate=#date(ToYear,12,31),
Source=List.Dates(
FromDate,
Duration.Days(ToDate-FromDate)+1,
#duration(1,0,0,0)
),
#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#“Renamed Columns” = Table.RenameColumns(#“Converted to Table”,{{“Column1”, “Date”}}),
#“Changed Type” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Date”, type date}}),
#“Inserted Year” = Table.AddColumn(#“Changed Type”, “Year”, each Date.Year([Date]), Int64.Type),
#“Inserted Start of Year” = Table.AddColumn(#“Inserted Year”, “Start of Year”, each Date.StartOfYear([Date]), type date),
#“Inserted End of Year” = Table.AddColumn(#“Inserted Start of Year”, “End of Year”, each Date.EndOfYear([Date]), type date),
#“Inserted Month” = Table.AddColumn(#“Inserted End of Year”, “Month”, each Date.Month([Date]), Int64.Type),
#“Inserted Start of Month” = Table.AddColumn(#“Inserted Month”, “Start of Month”, each Date.StartOfMonth([Date]), type date),
#“Inserted End of Month” = Table.AddColumn(#“Inserted Start of Month”, “End of Month”, each Date.EndOfMonth([Date]), type date),

#InsertIsEndOfMonth = Table.AddColumn(#InsertIsHoliday, “IsEndOfMonth”, each if [Date] = [End of Month] then true else false, type logical),

#"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
#"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
FiscalMonthBaseIndex=13-StartofFiscalYear,
adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
#"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
#"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
#"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
#"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Fiscal Year & Month", each Text.From([Fiscal Month])&" "&Text.From([Fiscal Year]))

in
#“Added Custom1”

Melissa · May 30, 2020, 6:26pm

No problem.
Added a second variation as well returning the EoM Date

let
// configurations start
FromYear = 2018, // set the start year of the date dimension. dates start from 1st of January of this year
ToYear=2021, // set the end year of the date dimension. dates end at 31st of December of this year

//ToDate = DateTime.Date(Date.EndOfYear(Date.AddYears(DateTime.LocalNow(),1)),
//dynamic to date

StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
firstDayofWeek=Day.Monday, // set the week’s start day, values: Day.Monday, Day, Sunday…
// configuration end
FromDate=#date(FromYear,1,1),
ToDate=#date(ToYear,12,31),
Source=List.Dates( FromDate, Duration.Days(ToDate-FromDate)+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
#"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
#"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),

InsertIsEndOfMonth = Table.AddColumn(#"Inserted End of Month", "IsEndOfMonth", each if [Date] = [End of Month] then true else false, type logical),
InsertEndOfMonthDate = Table.AddColumn(InsertIsEndOfMonth, "EndOfMonth Date", each if [Date] = [End of Month] then [Date] else null, type date),

#"Inserted Days in Month" = Table.AddColumn(InsertEndOfMonthDate, "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
#"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
FiscalMonthBaseIndex=13-StartofFiscalYear,
adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
#"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
#"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
#"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
#"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Fiscal Year & Month", each Text.From([Fiscal Month])&" "&Text.From([Fiscal Year]))
in
#"Added Custom1"

.
I hope this is helpful.

Hitman · May 30, 2020, 7:06pm

Melissa that appears to be working great but i will need to spend a little more time getting my head round.

I largely avoid on line forums but that has genuinely saved me a day of looking confused at the monitor and pulling a series of confused faces.

Melissa Rocks!

Melissa · May 30, 2020, 7:27pm

Glad I could help Andrew. If you have any follow up questions just report back… you’ll find this is a great community of users trying to help one another. So we’re glad to have you here.