topic Re: In Dax create a calculated column for last month & month prior, but exclude 3 months (Jun to Sept) in DAX Commands and Tips

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

ClemFandango — Thu, 27 Jun 2024 12:05:11 GMT

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,

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

lbendlin — Fri, 28 Jun 2024 01:08:06 GMT

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

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

Anonymous — Fri, 28 Jun 2024 02:52:14 GMT

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!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

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

ClemFandango — Fri, 28 Jun 2024 13:43:02 GMT

This is amazing Anonymous

Huge thanks!

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

ClemFandango — Fri, 28 Jun 2024 13:44:54 GMT

Hi lbendlin

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

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

ClemFandango — Mon, 08 Jul 2024 06:53:09 GMT

Hi Anonymous

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,

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

Anonymous — Mon, 08 Jul 2024 07:05:14 GMT

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

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

ClemFandango — Mon, 08 Jul 2024 08:44:04 GMT

Hi Anonymous

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,

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

Anonymous — Mon, 08 Jul 2024 09:03:14 GMT

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

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

ClemFandango — Tue, 09 Jul 2024 06:28:58 GMT

Thanks Anonymous

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

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

ClemFandango — Wed, 10 Jul 2024 13:30:25 GMT

Thanks for all of your help on this Anonymous . 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