cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Filtering week from Date Table

Hi Team,

I have the following data

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 ?

1 ACCEPTED SOLUTION
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

@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?

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors