Filtering week from Date Table

[Jos13]

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 ?

Thanks in advance

[Greg_Deckler]

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

[Jos13]

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

[Greg_Deckler · Accepted solution]

@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

[Jos13]

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

[amitchandak]

@Jos13 , see if these two blogs can help

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...