Forum Discussion

klehar's avatar
klehar
Icon for Helper V rankHelper V
1 year ago
Solved

Rolling 6 months dax

Hi,

 

I want to build a DAX flag for rolling 6 months that includes the current month

Can you please help

 

Example

 

Today is 8th Jan 2025 so Rolling 6 months flag would look like this

 

Calendar MonthCalendar YearRolling 6 Flag
20245No
20246No
20247No
20248Yes
20249Yes
202410Yes
202411Yes
202412Yes
20251Yes
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    klehar , Try using and create a new calculated column

    Rolling 6 Flag =
    VAR CurrentDate = TODAY()
    VAR CurrentYear = YEAR(CurrentDate)
    VAR CurrentMonth = MONTH(CurrentDate)
    VAR StartDate = EDATE(CurrentDate, -5) -- 5 months before the current month
    RETURN
    IF(
    [Calendar Year] * 100 + [Calendar Month] >= YEAR(StartDate) * 100 + MONTH(StartDate) &&
    [Calendar Year] * 100 + [Calendar Month] <= CurrentYear * 100 + CurrentMonth,
    "Yes",
    "No"
    )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    klehar , Try using and create a new calculated column

    Rolling 6 Flag =
    VAR CurrentDate = TODAY()
    VAR CurrentYear = YEAR(CurrentDate)
    VAR CurrentMonth = MONTH(CurrentDate)
    VAR StartDate = EDATE(CurrentDate, -5) -- 5 months before the current month
    RETURN
    IF(
    [Calendar Year] * 100 + [Calendar Month] >= YEAR(StartDate) * 100 + MONTH(StartDate) &&
    [Calendar Year] * 100 + [Calendar Month] <= CurrentYear * 100 + CurrentMonth,
    "Yes",
    "No"
    )

  • klehar's avatar
    klehar
    Icon for Helper V rankHelper V
    1 year ago

    can you please give me the pbi. Im unable to replicate. 

    Also if you can explain the logic in the IF part

    • bhanu_gautam's avatar
      bhanu_gautam
      Icon for Super User rankSuper User
      1 year ago

      klehar , Please find the PBIX file and I have written DAX now in more simplified way

       

      Rolling 6 Flag =
      VAR CurrentDate = TODAY()  -- Replace with TODAY() for dynamic calculation
      VAR StartDate = EOMONTH(CurrentDate, -5) + 1
      VAR EndDate = EOMONTH(CurrentDate, 0)
      RETURN
      IF(
          'DateTable'[Date] >= StartDate && 'DateTable'[Date] <= EndDate,
          "Yes",
          "No"
      )
       
      CurrentDate captures today's date.
      StartDate calculates the first day of the month 5 months before the current month.
      EndDate calculates the last day of the current month.
      The IF statement checks if the date in the Date table falls between StartDate and EndDate, and assigns "Yes" or "No" accordingly.