Reply
Jos13
Helper III
Helper III
Partially syndicated - Outbound

Filtering week from Date Table

Hi Team,

I have the following data

sales.png

and date table is defined as follows 

DateTable = CALENDAR(DATE(2020,3,1),DATE(2020,4,30))
and Week is calculated as
Week = "Week "&(WEEKNUM(DateTable[Date])-WEEKNUM(STARTOFMONTH(DateTable[Date])))+1
I have two dropdown filters for Month and Week from date table.
When I filter month, I could see all weeks from 1 to 5. My requirement is if I select March, I want Week 4 & Week 5 in week filter. Is that possible ?
 
Thanks in advance
1 ACCEPTED SOLUTION

Syndicated - Outbound

@Jos13 - Ah, that wasn't really clear from the original email. Well, a few ways of solving that. I suppose the easiest way would be to create a complex selector measure that returned 1 if there were sales in a wee and 0 if not and then just filter on that. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Syndicated - Outbound

@Jos13 - I am not quite following this one. Are you saying that you only want to display the last two weeks of any month in your week slicer?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Syndicated - Outbound

Hi @Greg_Deckler ,

It is not like that. I want to display those weeks which have sales. In the above example, I have sales for the last two weeks of March. So If I select March, I want week4 & 5. Similarly If I have sales for whole April, I want all the weeks in week slicer when April is selected.

If the current format is followed, sales will be blank when the user selects week 1,2 or 3 for March.

 

 

Thanks,

Jos

Syndicated - Outbound

@Jos13 - Ah, that wasn't really clear from the original email. Well, a few ways of solving that. I suppose the easiest way would be to create a complex selector measure that returned 1 if there were sales in a wee and 0 if not and then just filter on that. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Syndicated - Outbound

Thank you @Greg_Deckler for suggesting this. It worked for me 🙂

amitchandak
Super User
Super User

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)