Filter date ranges based on month and year (period)

[matusco]

Hello,

maybe easy to solve but for me not... 😞

I have SharePoint Online list with the employee absences. There is StartDate and EndDate and other metadata columns.

I'd like to have a report for HR department, where user can just select the year and the month and this two parameters filter the absences table. HR wants see only absences which affects selected salary month.

So, if I choose july - the 7th month, I'd like to get all SharePoint list items, if I choose june - the 6th month, I'd like to get only rows 2 and 4.

So, how to create slicer only with Year and Month and filter the date ranges?

Thanks in advance for your help.

Note> we are using date format DD.MM.YYYY

[Anonymous · Accepted solution]

Hi @matusco ,

I have created a simple sample, please refer to it to see if it helps you.

Create a measure first.

Table 2 = CALENDAR(DATE(2022,1,1),TODAY())

Then create a month column.

month = MONTH('Table 2'[Date])

Then put the field into the slicer.

Finally create a measure.

Measure =
VAR _selected =
    ( SELECTEDVALUE ( 'Table 2'[month] ) )
VAR _month =
    MONTH ( TODAY () )
RETURN
    IF (
        _selected = _month,
        1,
        IF ( _selected = MONTH ( MAX ( 'Table'[start date] ) ), 1, 0 )
    )

Put the measure into the filter, then select measure is 1.

If I have misunderstood your meaning ,please provide more details with your desired output.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@matusco , the Date format should be same as the system date format

refer

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

For the start date and end date refer to the blog or files attached

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...