cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

@ 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!:
Mastering Power BI 2nd Edition

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?

@ 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!:
Mastering Power BI 2nd Edition

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

@ 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!:
Mastering Power BI 2nd Edition

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

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