Modifying Month value within a time Dimension due to cross month week

[Roy_tap]

Hi,

I am currently having this Date Dimension created using Dax as follows:

Test_Date =

------------------------------------------------------------------------

VAR WeekNumberFlag = 1

VAR WeekDayFlag = 2

VAR CalendarYearStart = 2021

VAR CalendarYearEnd = YEAR(TODAY())

-------------------------------------------------------------------------

VAR CalendarBase = CALENDAR( DATE( CalendarYearStart , 1 , 1 ) ,

DATE( CalendarYearEnd , 12 , 31 ) )

//TODAY() )

RETURN

GENERATE (

CalendarBase,

VAR CalendarCurrentDate = [Date]

VAR CalendarYear = YEAR ( CalendarCurrentDate )

VAR CalendarMonth = MONTH ( CalendarCurrentDate )

VAR CalendarYearMonth = CalendarYear * 100 + CalendarMonth

VAR CalendarWeekOfYear = WEEKNUM( CalendarCurrentDate , WeekNumberFlag )

RETURN ROW (

"Year" , CalendarYear ,

"MonthCaption", FORMAT ( CalendarCurrentDate, "mmm" ) ,

"WeekName", "W" & CalendarWeekOfYear ,

"YearMonth", CalendarYear & "." & (if (LEN(CalendarMonth) < 2, "0" & CalendarMonth, CalendarMonth)),

"YearWeek" , CalendarYear * 100 + CalendarWeekOfYear

)

)

The script generate all the standard date formats that was required for my report, however the problem arise when i have report using both Monthcaption and weekname as some of the records will be filter off due to the cross week between different Months.

Following is the result of my date dimension table which was filtered with the problem i had for the cross month:

Between the period of 1/31/2021 to 2/6/2021, it was defined as W6 under WeekName, however, they resides under Month of Jan and Feb as shown above, so when i display the report based on WeekName alone, the result will show correctly, however, when i added the Month column, the records on 1/31/2021 will be filtered off.

How can i add a new Month column that will change the Jan to Feb for 1/31/2021? As this problem will persists for all the cross months within the date dimension, i was thinking whether there is such as way that the month can be modify accordingly based on the count of WeekName and YearMonth.

For e.g. in this case, if we group the following, we should be getting the following count
MonthCaption | WeekName | YearMonth | Count
Jan | W6 | 2021.01 | 1
Feb | W6 | 2021.02 |  6

Then modify Jan to Feb.

However, if the group count if higher on Jan over Feb as shown, below, the month should be modify to Jan instead:

MonthCaption | WeekName | YearMonth | Count
Jan | W5 | 2020.01 | 6
Feb | W5 | 2020.02 |  1

Is there any suggestion on how we can achieve the modication of the month value based on above assumption?

Just a point to note, we understand that we can leverage the use of the ISO Weekly calendar of  4-4-5 or 5-4-4 format, however, due to the additional/missing week that might arise in the future, we are trying to explore other alternative as indicated above. 

Thanks.

[v-yiruan-msft · Accepted solution]

Hi @Roy_tap ,

You can create two calculated columns as below to get the updated month name, please find the attachment for the details.

1. Create weekday field

Weekday = WEEKDAY('Test_Date'[Date],2)

2. Create another calculated column to get the updated month name

New Month =
VAR _minmonth =
    MONTH (
        CALCULATE (
            MIN ( 'Test_Date'[Date] ),
            FILTER (
                'Test_Date',
                'Test_Date'[Year] = EARLIER ( 'Test_Date'[Year] )
                    && 'Test_Date'[WeekName] = EARLIER ( 'Test_Date'[WeekName] )
            )
        )
    )
VAR _maxmonth =
    MONTH (
        CALCULATE (
            MAX ( 'Test_Date'[Date] ),
            FILTER (
                'Test_Date',
                'Test_Date'[Year] = EARLIER ( 'Test_Date'[Year] )
                    && 'Test_Date'[WeekName] = EARLIER ( 'Test_Date'[WeekName] )
            )
        )
    )
VAR _weekday =
    CALCULATE (
        MIN ( 'Test_Date'[Weekday] ),
        FILTER (
            'Test_Date',
            'Test_Date'[Year] = EARLIER ( 'Test_Date'[Year] )
                && 'Test_Date'[WeekName] = EARLIER ( 'Test_Date'[WeekName] )
                && DAY ( 'Test_Date'[Date] ) = 1
        )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'Test_Date'[Date] ),
        FILTER (
            'Test_Date',
            'Test_Date'[Year] = EARLIER ( 'Test_Date'[Year] )
                && 'Test_Date'[WeekName] = EARLIER ( 'Test_Date'[WeekName] )
        )
    )
VAR _maxdate =
    CALCULATE (
        MAX ( 'Test_Date'[Date] ),
        FILTER (
            'Test_Date',
            'Test_Date'[Year] = EARLIER ( 'Test_Date'[Year] )
                && 'Test_Date'[WeekName] = EARLIER ( 'Test_Date'[WeekName] )
        )
    )
RETURN
    IF (
        _minmonth = _maxmonth,
        'Test_Date'[MonthCaption],
        IF ( _weekday < 4, FORMAT ( _maxdate, "mmm" ), FORMAT ( _mindate, "mmm" ) )
    )

Best Regards

[Roy_tap]

Thanks.... very nicely done column that is exactly what i needed. 😊

[amitchandak]

@Roy_tap , two way to deal based on what I got, based on week start or end

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

End month = eomonth([date],0)

New month end = if([Week Start Date]<= eomonth([Date],-1)+1 && [Week End Date] >=eomonth([date],-1),eomonth([date],-1),eomonth([date],0))

This will extend month end 

or

Change Month week

Start Month = STARTOMONTH('Date'[Date])
WeekDay = WEEKDAY([Date],2) //monday
Start of Week = [Date] -[WeekDay]+1 //monday
Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1