Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Abhijsrwala1
Frequent Visitor

Advanced Filter

How do I create a filter that has Jan,feb as well as This month, last fiscal year, as its elementn.

Note : This month, last fiscal year are not in dataset, has to be calculated and included as slicer elements.Capture.PNG

1 ACCEPTED SOLUTION
v-yanjiang-msft
Community Support
Community Support

Hi @Abhijsrwala1 ,

The option in slicer must be the value in the column, so you should create a new table.

vkalyjmsft_0-1652174926591.png

Slicer table =
UNION (
    VALUES ( 'Table'[Month] ),
    ROW ( "Month", "This month" ),
    ROW ( "Month", "Last fiscal year" )
)

Get the result.

vkalyjmsft_1-1652175009214.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

View solution in original post

3 REPLIES 3
v-yanjiang-msft
Community Support
Community Support

Hi @Abhijsrwala1 ,

The option in slicer must be the value in the column, so you should create a new table.

vkalyjmsft_0-1652174926591.png

Slicer table =
UNION (
    VALUES ( 'Table'[Month] ),
    ROW ( "Month", "This month" ),
    ROW ( "Month", "Last fiscal year" )
)

Get the result.

vkalyjmsft_1-1652175009214.png

I attach my sample below for reference.

 

Best Regards,
Community Support Team _ kalyj

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

Abhijsrwala1
Frequent Visitor

@amitchandak I understand, but once those values are calculated how to bring that under one slicer along with rest of the values. As you can see. 01-Jan and calculated values are together in one slicer.
Capture.PNG

amitchandak
Super User
Super User

@Abhijsrwala1 , Based on what I got, You can have year behind measures

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

or last year same month

 

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.