Forum Discussion

Post Partisan

4 years ago

Solved

Last 6 months column

I have a column in Calender table to identify last six months and the formula is-

6 months =

IF (

YEAR ( 'Calendar'[Date] ) = YEAR ( MAX('Calendar'[Date] ))

&& MONTH ( 'Calendar'[Date] ) = MONTH (MAX('Calendar'[Date])),

'Calendar'[MonthYear],

IF (

YEAR ('Calendar'[Date]) = YEAR ( MAX('Calendar'[Date]))

&& MONTH ('Calendar'[Date]) = MONTH (MAX('Calendar'[Date]))-1,

'Calendar'[MonthYear],

IF (

YEAR ('Calendar'[Date]) = YEAR ( MAX('Calendar'[Date]))

&& MONTH ('Calendar'[Date]) = MONTH (MAX('Calendar'[Date]))-2,

'Calendar'[MonthYear],IF (

YEAR ('Calendar'[Date]) = YEAR ( MAX('Calendar'[Date]))

&& MONTH ('Calendar'[Date]) = MONTH (MAX('Calendar'[Date]))-3,

'Calendar'[MonthYear],IF (

YEAR ('Calendar'[Date]) = YEAR ( MAX('Calendar'[Date]))

&& MONTH ('Calendar'[Date]) = MONTH (MAX('Calendar'[Date]))-4,

'Calendar'[MonthYear],IF (

YEAR ('Calendar'[Date]) = YEAR ( MAX('Calendar'[Date]))

&& MONTH ('Calendar'[Date]) = MONTH (MAX('Calendar'[Date]))-5,

'Calendar'[MonthYear],

"Neither"

)

)))))

Now, it was working well till December but now when It reached Jan 2022, It is just shoowing Jan 2022 as current month and none for the the last 5 months as my formula says if year calender Year =Year Max calender Date.

How can I adjust this formula?

My calender Year has this below structure-

Anonymous
4 years ago
learner03 maybe I'm not fully understanding what you're trying to do, but it seems to me that your formula could be simplified significantly to something like :

Last 6 Months = VAR _CurrentDate = MAX('Calendar'[Date]) VAR _Result = IF( DATEDIFF('Calendar'[Date],_CurrentDate,MONTH) < 6, 'Calendar'[MonthYear], "Neither" ) Return _Result

Try this:

6 months =
VAR _A =
    MAX ( 'Calendar'[Date] )
VAR _AM =
    MONTH ( _A )
VAR _6M =
    IF (
        _AM >= 6,
        DATE ( YEAR ( _A ), _AM - 5, 01 ),
        DATE ( YEAR ( _A ) - 1, 12 - ( 5 - _AM ), 01 )
    )
RETURN
    IF (
        'Calendar'[Date] >= _6M
            && 'Calendar'[Date] <= _A,
        'Calendar'[MonthYear],
        "Neither"
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

2 Replies

Anonymous
Not applicable
4 years ago
learner03 maybe I'm not fully understanding what you're trying to do, but it seems to me that your formula could be simplified significantly to something like :

Last 6 Months = VAR _CurrentDate = MAX('Calendar'[Date]) VAR _Result = IF( DATEDIFF('Calendar'[Date],_CurrentDate,MONTH) < 6, 'Calendar'[MonthYear], "Neither" ) Return _Result

Super User

Try this:

6 months =
VAR _A =
    MAX ( 'Calendar'[Date] )
VAR _AM =
    MONTH ( _A )
VAR _6M =
    IF (
        _AM >= 6,
        DATE ( YEAR ( _A ), _AM - 5, 01 ),
        DATE ( YEAR ( _A ) - 1, 12 - ( 5 - _AM ), 01 )
    )
RETURN
    IF (
        'Calendar'[Date] >= _6M
            && 'Calendar'[Date] <= _A,
        'Calendar'[MonthYear],
        "Neither"
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Related content