Set Between Date Slicer to First and End of Month

[bigmac025]

Hello,

I am having problems setting a Between Date Slicer to automatically start on the first day of the current month and end on the end of the current month.

I thought I had done this before with sucess a long time ago but no luck with this current report I am working on.  

I tried something like this:

IsCurrentMonth =
VAR StartOfMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR EndOfMonth   = EOMONTH(TODAY(), 0)
RETURN
IF (
    DatesTable[Date] >= StartOfMonth &&
    DatesTable[Date] <= EndOfMonth,
    1,
    0
)

Then I filtered the date slicer on "1".  This worked to set the date slicer to my DatesTable but since the date I am trying to filter on  is in another table, it didn't work for the visual.  I tried creating a relationship between the date in the other table (called Tickets, field name called Created Date) and my dates table but had no luck either. 

If helpful, I can also post my pbix file.

Appreciate any help.

Thanks,

Tim  

[bigmac025]

@danextian I figured out why slicer on the date column wasn't working.  I needed to match the date format on both tables.  The date in the date table was set to short date.  I then created a date in my Ticket table that just had the date only (no time) and formatted it to short date.  Lastly, I had to click on the date slicer and select the beginning of the month.  It worked then.

Thanks to all for the help.

[cengizhanarslan · Accepted solution]

Power BI can’t “auto-set” a Between slicer’s start/end to the current month. The workaround is to use a Relative date slicer or filter visuals with a measure/flag.

Best practice here:

1. Create a proper Date table (marked as date table).

2. Relate DatesTable[Date] → Tickets[Created Date] (many-to-one, single direction, date type on both).

3. Use DatesTable[Date] in the slicer (not Tickets[Created Date]).

Then choose one of these:

Option A (recommended): Relative date slicer

• Change slicer type to Relative

• Set it to “This month”
  This will always stay on the current month and will filter Tickets through the relationship.

Option B: Current-month flag on the Date table
Your flag idea is fine, but apply it on the Date table slicer (DatesTable[Date]) and ensure the relationship exists:

IsCurrentMonth =
VAR StartOfMonth = DATE ( YEAR ( TODAY() ), MONTH ( TODAY() ), 1 )
VAR EndOfMonth   = EOMONTH ( TODAY(), 0 )
RETURN
IF ( DatesTable[Date] >= StartOfMonth && DatesTable[Date] <= EndOfMonth, 1, 0 )

Then filter the slicer (or page) to IsCurrentMonth = 1.

If the relationship “had no luck”, the usual causes are:

• Tickets[Created Date] is DateTime (has time) while Date table is Date
  → create Tickets[Created Date (Date)] = DATEVALUE(Tickets[Created Date]) and relate that.

• Date table not marked as date table / wrong data type.

Using Relative slicer (“This month”) + correct relationship is the cleanest solution.

[bigmac025]

Thank you both @AshokKunwar and @danextian for your help.  I will use this information and see if I can get my date slicer behavior to work as I want it.  I will follow up if either answer solves.

[AshokKunwar]

Hi @bigmac025 

Subject: [SOLVED] Automatically Set Between Date Slicer to Start/End of Current Month

The Challenge:

Standard "Between" date slicers in Power BI usually stick to the dates they were last saved with. Forcing them to automatically update to the first and last day of the current month requires a dynamic way to identify the current period and a proper model relationship to ensure the filter affects your data.

The Solution:

The most efficient way to solve this is to use a Month Offset column in your Dates table. This creates a relative reference that stays at "0" for the current month, regardless of the today's date.

Step 1: Check Your Model Relationships

Before adding DAX, ensure your DatesTable is related to your Tickets table (or whatever fact table you are using).

Go to the Model View.

Connect DatesTable[Date] to Tickets[Created Date].

Confirm it is a One-to-Many relationship with a Single filter direction.

Step 2: Create a Dynamic Month Offset

Add this Calculated Column to your DatesTable. This is much more performant than complex measures in a slicer.

MonthOffset = 

(YEAR(DatesTable[Date]) * 12 + MONTH(DatesTable[Date])) - 

(YEAR(TODAY()) * 12 + MONTH(TODAY()

))

• 0: Represents the current month.
• -1: Represents the previous month.

Step 3: Apply the Filter to the Slicer Visual

Select your Between Date Slicer visual.

Open the Filters Pane.

Drag the new MonthOffset column into the "Filters on this visual" bucket.

Set the filter to Basic filtering and check the box for 0.

Why this works:

Stability: The "Between" slicer is now limited only to dates belonging to the current month.

Auto-Update: When the calendar rolls over to a new month, the calculation for MonthOffset = 0 automatically updates to the new dates, and your slicer follows suit.

Model Propagation: Because of the relationship you built in Step 1, filtering the DatesTable slicer will correctly filter the Created Date in your Tickets table.

If this relative month trick helped you automate your report, please mark this as an "Accepted Solution" to help others!

Best regards, 

Vishwanath 

[danextian]

Hi @bigmac025 

If it works with the date column from your Dates table, what’s stopping you from using that instead of the column from your fact table? Putting that aside, it’s generally better to use a dimension to filter range slicers, since slicers can behave unpredictably when driven by a measure. You could turn your current-month measure into a calculated column in the Dates table and use that as a visual-level filter.