Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculated column to show past month and this week

Hello everyone, 

I have a calendar auto table that compromises of date tables.


I would like to have a calculated column that is something like this.

If the dates are in the current month, it will show on a weekly formated (refer to week-01 Dec 2024 and week-08 Dec 2024). If it's previous dates, it will be grouped by monthly basis (refer to Aug 2024, Sep 2024, etc).

Month Aug'24Month Sep'24Month Oct'24Month Nov'24Week 01-Dec-2024Week 08-Dec-2024


I have already made a calculated column to show the week format.

Week_Label =
VAR WeekStart = DATEDIFF(DATE(1900, 1, 1), 'Calendar'[Date], DAY)
VAR WeekDayValue = WEEKDAY('Calendar'[Date], 2)
VAR StartOfWeek = 'Calendar'[Date] - WeekDayValue + 1
VAR StartOfNextWeek = 'Calendar'[Date] - WeekDayValue + 8
RETURN
    IF(
        WeekDayValue <= 3,
        "Week " & FORMAT(StartOfWeek, "dd-MMM-yyyy"),
        "Week " & FORMAT(StartOfNextWeek, "dd-MMM-yyyy")
    )


I'm currently stuck at making the formula above to include the month clause as I stated above.

Let me know if there's anything else needed from my end.

Thanks!

Need Help
Show and Tell
  • anmolmalviya05's avatar
    anmolmalviya05
    1 year ago

    Hi Anonymous , please try the formula below:

    Group_Label =
    VAR CurrentMonthStart = EOMONTH(TODAY(), -1) + 1
    VAR CurrentDate = 'Calendar'[Date]
    VAR IsCurrentMonth = CurrentDate >= CurrentMonthStart && CurrentDate <= EOMONTH(TODAY(), 0)
    VAR WeekDayValue = WEEKDAY(CurrentDate, 2)
    VAR WeekStart = CurrentDate - WeekDayValue + 1
    VAR WeekLabel =
    IF(
    WeekDayValue <= 3,
    "Week " & FORMAT(WeekStart, "dd-MMM-yyyy"),
    "Week " & FORMAT(WeekStart + 7, "dd-MMM-yyyy")
    )
    RETURN
    IF(
    IsCurrentMonth,
    WeekLabel, -- Weekly format for the current month
    FORMAT(CurrentDate, "MMM yyyy") -- Monthly format for previous months
    )

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi Anonymous 
    Create a new calculated column that groups dates into "weekly format" for the current month and "monthly format" for previous months. Here's how to modify your DAX code:

    Date_Grouping =
VAR CurrentMonth =
    FORMAT(TODAY(), "YYYY-MM")
VAR DateMonth =
    FORMAT('Calendar'[Date], "YYYY-MM")
VAR WeekStart =
    'Calendar'[Date] - WEEKDAY('Calendar'[Date], 2) + 1
VAR StartOfWeekLabel =
    "Week " & FORMAT(WeekStart, "dd-MMM-yyyy")
RETURN
    IF(
        DateMonth = CurrentMonth,
        StartOfWeekLabel,
        FORMAT('Calendar'[Date], "MMM YYYY")
    )

3 Replies

  • anmolmalviya05's avatar
    anmolmalviya05
    Super User
    1 year ago

    Hi Anonymous , please try the formula below:

    Group_Label =
    VAR CurrentMonthStart = EOMONTH(TODAY(), -1) + 1
    VAR CurrentDate = 'Calendar'[Date]
    VAR IsCurrentMonth = CurrentDate >= CurrentMonthStart && CurrentDate <= EOMONTH(TODAY(), 0)
    VAR WeekDayValue = WEEKDAY(CurrentDate, 2)
    VAR WeekStart = CurrentDate - WeekDayValue + 1
    VAR WeekLabel =
    IF(
    WeekDayValue <= 3,
    "Week " & FORMAT(WeekStart, "dd-MMM-yyyy"),
    "Week " & FORMAT(WeekStart + 7, "dd-MMM-yyyy")
    )
    RETURN
    IF(
    IsCurrentMonth,
    WeekLabel, -- Weekly format for the current month
    FORMAT(CurrentDate, "MMM yyyy") -- Monthly format for previous months
    )

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi Anonymous 
    Create a new calculated column that groups dates into "weekly format" for the current month and "monthly format" for previous months. Here's how to modify your DAX code:

    Date_Grouping =
VAR CurrentMonth =
    FORMAT(TODAY(), "YYYY-MM")
VAR DateMonth =
    FORMAT('Calendar'[Date], "YYYY-MM")
VAR WeekStart =
    'Calendar'[Date] - WEEKDAY('Calendar'[Date], 2) + 1
VAR StartOfWeekLabel =
    "Week " & FORMAT(WeekStart, "dd-MMM-yyyy")
RETURN
    IF(
        DateMonth = CurrentMonth,
        StartOfWeekLabel,
        FORMAT('Calendar'[Date], "MMM YYYY")
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you so much!