topic Re: Week Number parameters in DAX Commands and Tips

Week Number parameters

Anonymous — Sun, 07 Aug 2022 14:44:08 GMT

Hi,

I'm working on a weekly production plan, I created a date table with the week numbers (Formula: Weeknum)
My working week starts on Sunday and ends on Saturday.

There are weeks that are situated in 2 months

Example 01: Week 32 , start in Sunday 31/07/2022 ,ends 06/08/2022 , 1 day in July and 6 days in August . In this case Dax considered this week in July --it's not correct for me

I want to apply this parameter when retrieving the week number in case the week is situated in 2 different months.

- (Case Example 01) The Week 32 will be conidered in July if the number of days of this week are =>3 are in July , else considere in August.

How I can do this ?

Re: Week Number parameters

tamerj1 — Sun, 07 Aug 2022 15:19:56 GMT

Hi Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/SfZdZRuR9IMEblZN

You need to create a new month column in the date table to use as slicer, as follows:

Slicer Month = VAR CurrentMonth = 'Date'[Month Number] VAR CurrentYearWeekTable = FILTER ( CALCULATETABLE ( 'Date', ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Week Number] ) ), 'Date'[Month Number] = CurrentMonth ) VAR MaxWeekday = MAXX ( CurrentYearWeekTable, WEEKDAY ( 'Date'[Date], 1 ) ) VAR MonthNumber = IF ( MaxWeekday < 3, CurrentMonth + 1, CurrentMonth ) RETURN FORMAT ( DATE ( 1, MonthNumber, 1 ), "MMMM" )

Re: Week Number parameters

Anonymous — Sun, 07 Aug 2022 16:03:26 GMT

Thank you tamerj1 for your reponse.

just one detail ,how I can convert "Slicer Month" to a Date Format, now it is a Text ?

Re: Week Number parameters

tamerj1 — Sun, 07 Aug 2022 17:34:17 GMT

Anonymous

Do you mean you want to show the first or last day of the month?

Re: Week Number parameters

Anonymous — Sun, 07 Aug 2022 18:18:40 GMT

Hi tamerj1 ,I mean to put slicer in descending order in the visual (report) like: January, February, March.....SO I must put the format as a dat

Re: Week Number parameters

tamerj1 — Mon, 08 Aug 2022 05:04:35 GMT

Hi Anonymous
Create another column (Slicer Month Number) and then use the option sort by column to sort the Slicer Month by the Slicer Month Number