cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

## 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-

2 ACCEPTED SOLUTIONS
Solution Sage

@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.

2 REPLIES 2
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.

Solution Sage

@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``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.