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

Helper III

## In Dax create a calculated column for last month & month prior, but exclude 3 months (Jun to Sept)

Hi all,

I am hoping one of you geniuses can help. I have about 10million+ rows and trying to create a calculated column that returns “yes” where the ‘MonthofDateAction’ is ‘last month’ & ‘2months ago’ from ‘Date Today’ – the problem I am having is that I need to also exclude months June, July & Aug i.e. (MonthAction 6,7,8).

So, if ‘Date Today’ was either September, August, July or June, then the ‘Last Month’ would be May and ‘2months ago’ would be April.

In the Table1 example below, if the ‘Date Today’ was August, then the ‘last month’ would be “Yes” where MonthofDateAction = 5 (i.e May).

 ID Date Today DateAction MonthofDateAction Exclude Last Month 2months ago 1 27/08/2024 25/05/2024 5 No Yes No 1 27/08/2024 26/07/2024 7 Yes No No 2 27/08/2024 01/03/2024 3 No No No 4 27/08/2024 01/04/2024 4 No No Yes

However, if the 'date today' changed to May, ‘last month’ would be “Yes” where MonthofDateAction = 4 (i.e Apr).

 ID Date Today DateAction MonthofDateAction Exclude Last Month 2months ago 1 27/05/2024 25/05/2024 5 No No No 1 27/05/2024 26/07/2024 7 Yes No No 2 27/05/2024 01/03/2024 3 No No Yes 4 27/05/2024 01/04/2024 4 No Yes No

I am trying to create ‘Last Month’ & ‘2months ago’ as calculated columns. Any ideas eternally appreciated.

Many thanks,

CF

1 ACCEPTED SOLUTION
Community Support

Hi, @ClemFandango

Thanks for @lbendlin reply. You can refer to the following dax to solve the problem.

``````Exclude =
VAR _excludeMonth = { 6, 7, 8 }
VAR _result =
IF ( 'Table'[MonthofDateAction] IN _excludeMonth, "Yes", "No" )
RETURN
_result

Last Month =
VAR _MonthofDateToday =
MONTH ( 'Table'[Date Today] )
VAR _month = { 6, 7, 8, 9 }
VAR _lastMonth =
IF ( _MonthofDateToday IN _month, 5, _MonthofDateToday - 1 )
VAR _result =
IF ( _lastMonth = 'Table'[MonthofDateAction], "Yes", "No" )
RETURN
_result

2 months ago =
VAR _MonthofDateToday =
MONTH ( 'Table'[Date Today] )
VAR _month1 = { 6, 7, 8, 9 }
VAR _month2 = { 10 }
VAR _lastMonth =
IF (
_MonthofDateToday IN _month1,
4,
IF ( _MonthofDateToday IN _month2, 5, _MonthofDateToday - 2 )
)
VAR _result =
IF ( _lastMonth = 'Table'[MonthofDateAction], "Yes", "No" )
RETURN
_result
``````

Best Regards,
Yang

Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Thanks a lot!

10 REPLIES 10
Community Support

Hi, @ClemFandango

Thanks for @lbendlin reply. You can refer to the following dax to solve the problem.

``````Exclude =
VAR _excludeMonth = { 6, 7, 8 }
VAR _result =
IF ( 'Table'[MonthofDateAction] IN _excludeMonth, "Yes", "No" )
RETURN
_result

Last Month =
VAR _MonthofDateToday =
MONTH ( 'Table'[Date Today] )
VAR _month = { 6, 7, 8, 9 }
VAR _lastMonth =
IF ( _MonthofDateToday IN _month, 5, _MonthofDateToday - 1 )
VAR _result =
IF ( _lastMonth = 'Table'[MonthofDateAction], "Yes", "No" )
RETURN
_result

2 months ago =
VAR _MonthofDateToday =
MONTH ( 'Table'[Date Today] )
VAR _month1 = { 6, 7, 8, 9 }
VAR _month2 = { 10 }
VAR _lastMonth =
IF (
_MonthofDateToday IN _month1,
4,
IF ( _MonthofDateToday IN _month2, 5, _MonthofDateToday - 2 )
)
VAR _result =
IF ( _lastMonth = 'Table'[MonthofDateAction], "Yes", "No" )
RETURN
_result
``````

Best Regards,
Yang

Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Thanks a lot!

Helper III

Many thanks for the code above. I have just noticed something though

If the 'DateAction' is defined as May, it will return "Yes" for all items in May for years 2025, 2024 2023 etc.

But, i need to return "Yes" for items that are 'last month' of the same year only.

Any idea of the changes required to do this?

Many thanks,

CF

Community Support

Hi, @ClemFandango

Please give me some data and the results you expect that I use for testing purposes. Will there be a cross year split.

Best Regards,
Yang

Community Support Team

Helper III

To sumamrise, we are calculating 'last month' & '2 months ago' based on ‘Date Today’ and excluding September, August, July or June.

Your method works beautifully, except for where there is a multipe years against the same month. Both 'Last Month' & '2months ago' should be based on the current year from 'Date Today'.

I have added some extra data to the examples below for 'ID' 5.

The current formula will incorrectly return "Yes" on ID 5 for "Last Month" where 'DateAction' is 2023

 ID Date Today DateAction MonthofDateAction Exclude Last Month 2months ago 1 27/08/2024 25/05/2024 5 No Yes No 1 27/08/2024 26/07/2024 7 Yes No No 2 27/08/2024 01/03/2024 3 No No No 4 27/08/2024 01/04/2024 4 No No Yes 5 27/08/2024 25/05/2023 5 No No No 5 27/08/2024 25/05/2024 5 No Yes No

And, if the 'date today' changed to May, the ‘last month’ would be “Yes” where MonthofDateAction = 4 (i.e Apr). But, the current formula will incorrectly return "Yes" for 'Last Month' & '2months ago' (ID 5) when DateAction is 2023.

 ID Date Today DateAction MonthofDateAction Exclude Last Month 2months ago 1 27/05/2024 25/05/2024 5 No No No 1 27/05/2024 26/07/2024 7 Yes No No 2 27/05/2024 01/03/2024 3 No No Yes 4 27/05/2024 01/04/2024 4 No Yes No 5 27/05/2024 01/03/2023 3 No No No 5 27/05/2024 01/04/2023 4 No No No 5 27/05/2024 01/04/2024 7 No Yes No 5 27/05/2024 01/03/2024 7 No No Yes

Hope this makes sense

Many thanks,

CF

Community Support

Hi, @ClemFandango

Adding a year level filter to each dax will work.

``````Last Month =
VAR _MonthofDateToday =
MONTH ( 'Table'[Date Today] )
VAR _month = { 6, 7, 8, 9 }
VAR _yearDatetoday = YEAR('Table'[Date Today])
VAR _yearDateAction = YEAR('Table'[Date Action])

VAR _lastMonth =
IF ( _MonthofDateToday IN _month , 5, _MonthofDateToday - 1 )
VAR _result =
IF ( _lastMonth = 'Table'[MonthofDateAction] && _yearDatetoday = _yearDateAction , "Yes", "No" )
RETURN
_result

2 months ago =
VAR _MonthofDateToday =
MONTH ( 'Table'[Date Today] )
VAR _month1 = { 6, 7, 8, 9 }
VAR _month2 = { 10 }
VAR _yearDatetoday = YEAR('Table'[Date Today])
VAR _yearDateAction = YEAR('Table'[Date Action])
VAR _lastMonth =
IF (
_MonthofDateToday IN _month1,
4,
IF ( _MonthofDateToday IN _month2  , 5, _MonthofDateToday - 2 )
)
VAR _result =
IF ( _lastMonth = 'Table'[MonthofDateAction] && _yearDatetoday = _yearDateAction, "Yes", "No" )
RETURN
_result

Exclude =
VAR _excludeMonth = { 6, 7, 8 }
VAR _yearDatetoday = YEAR('Table'[Date Today])
VAR _yearDateAction = YEAR('Table'[Date Action])
VAR _result =
IF ( 'Table'[MonthofDateAction] IN _excludeMonth && _yearDatetoday = _yearDateAction, "Yes", "No" )
RETURN
_result
``````

Best Regards,
Yang

Community Support Team

Helper III

Thanks for all of your help on this @v-yaningy-msft . You helped me massivley in finding the complete solution that is....

``````Last_Month =
VAR _MonthofDateToday =
EDATE([Date Today].[MonthNo],0)
VAR _month = { 6, 7, 8, 9 }
Var _DateAction2 = Edate([Date Action],0)
VAR _yearDatetoday = YEAR([Date Today])
VAR _yearDateAction = YEAR([Date Action])

VAR _lastMonth =
IF ( _MonthofDateToday IN _month, 5, IF(Month([Date Today])>1,MONTH([Date Today])-1,12))
VAR _result =
IF ( _lastMonth = [Monthofdatecation] && _yearDatetoday = _yearDateAction || MONTH(Date Action) =12 && MONTH([Date Today])=1 , "Yes","No" )
RETURN
_result

2monthsago =
VAR _MonthofDateToday =
EDATE([Date Today].[MonthNo],0)
VAR _month1 = { 6, 7, 8, 9 }
VAR _month2 = { 10 }
Var _DateAction2 = Edate([Date Action],0)
VAR _yearDatetoday = YEAR([Date Today])
VAR _yearDateAction = YEAR([Date Action])
VAR _lastMonth =
IF (
_MonthofDateToday IN _month1,
4,
IF ( _MonthofDateToday IN _month2  , 5, IF(Month([Date Today])>1,MONTH([Date Today])-2,12))
)
VAR _result =
IF ( _lastMonth = [Monthofdatecaction] && _yearDatetoday = _yearDateAction || MONTH([Date Action]) =11 && MONTH([Date Today])=1 || MONTH([Date Action]) =12 && MONTH([Date Today])=2, "Yes","No" )
RETURN
_result``````

Helper III

Thanks @v-yaningy-msft

Amazing!!

Apologies though, as i have spotted another bit of logic required (I think this is the cross year split that you were reffering to?). The dax is unable to handle the transition of the year automatically when subtracting e.g. 2 month

If the 'Date Today' was in January 2024, then I would like 'Last Month' to return "Yes" where the 'DateAction' is December 2023. And '2months ago' would return "Yes" where the 'DateAction is November 2023.

 ID Date Today DateAction MonthofDateAction Exclude Last Month 2months ago 1 01/01/2024 25/05/2024 5 No No No 1 01/01/2024 26/07/2024 7 Yes No No 2 01/01/2024 01/03/2024 3 No No No 4 01/01/2024 01/04/2024 4 No No No 5 01/01/2024 25/05/2023 5 No No No 5 01/01/2024 25/05/2024 5 No No No 6 01/01/2024 05/12/2023 12 No Yes No 6 01/01/2024 07/11/2023 11 No No Yes

Thanks so much again, i really do appreciate your help & guidance

Helper III

This is amazing @v-yaningy-msft

Huge thanks!

Super User

why does it have to be a calculated column?  What are you doing with the data next?

Helper III

This is a very small bit of logic for a much larger peice that calculates a 'weighted scoring'

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.