Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Week Number parameters

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 ?

 

 

 

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you tamerj1  for your reponse.

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

  • tamerj1's avatar
    tamerj1
    4 years ago

    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

    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
    MonthNumber

5 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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" )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you tamerj1  for your reponse.

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      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

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        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

        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
    MonthNumber