Using DAX to calculate Last Month and 2 months ago with date transition between Jan 24 and Dec 23

[ClemFandango]

Hi all,

I thought I would start a new thread for this. @v-yaningy-msft has helped immensely by providing the code below. The code works well, but unfortunately it is unable to handle the transition of the year when subtracting months if the ‘Date Today’ is January 24 (or February 24).

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

Bit of background…

The code above is used in a calculated column that returns “yes” where the ‘MonthofDateAction’ is ‘last month’ & ‘2months ago’ from ‘Date Today’. It also excludes 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.

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

This works well, except for when there is a transition in year from 2024 ‘Date Today’ to 2023 ‘Date Action’. As an example If the ‘Date Today’ was January 24, then the ‘last month’ should be “Yes” where MonthofDateAction = 12 (i.e. December), and ‘2months ago’ would be “Yes” where MonthofDateAction = 11 (i.e. November)

The problem…

This is an example of Table1 and I am trying to tweak the code above for ‘Last Month’ & ‘2months ago’ to generate the results for ID 6 that returns “Yes” (see below). The DAX will currently return “No” for ‘LastMonth’ and ‘2monthsago’ for ID 6, due to the transition of year from Jan 24 (Date Today) to Dec 23 (Date Action).

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/2023	7	Yes	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

Many thanks for reading & I am eternally grateful for any ideas or help,

CF

[v-tianyich-msft]

Hi @ClemFandango ,

I don't fully understand what you mean, could you provide sample data and expected results?

Best regards,
Community Support Team_ Scott Chang

[ClemFandango]

Hi @v-tianyich-msft 

Many thanks for your reply. I am super grateful for any help!

I am trying to return "Yes" for 'Last Month' where the 'DateAction' is in the 'Last Month' of the 'Date Today'.

As an example if the if the 'Date Today' was 01/01/2024, then the last month would be "yes" for anything with a 'DateAction' in Decemeber 2023. And if the 'Date Today was 01/01/2024, then '2months ago' would be "yes" for anything with a 'DateAction' in November 2023 - This bit is not working in the code above, due to the transition of year from Jan 2024 into Dec 2023

Additionally I need to not count the months of June, July & Aug. i.e if the 'Date Today' was 01/09/2024, then the last month would be "yes" for anything with a 'DateAction' in May 2024 - This is working bit is working in the code above.

This is my data within table1 and I am trying to create calculated columns for 'Last Month' and '2months ago' returning the reselts below

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/2023	7	Yes	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

[v-tianyich-msft]

Hi @ClemFandango ,

I made simple samples and you can check the results below:

Last Month = IF(ABS(DATEDIFF([Date Today],[DateAction],DAY))<30,"Yes","No")
Two Month = IF(ABS(DATEDIFF([Date Today],[DateAction],DAY))>30&&ABS(DATEDIFF([Date Today],[DateAction],DAY))<60,"Yes","No")

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[ClemFandango]

Hi there, 

thanks @v-tianyich-msft 

Unfortunatley this also returns "Yes" if the 'DateAction' is within 30 days of the 'LastMonth' and the following month. I have added two extra rows that show the results that I am trying to get.

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/2023	7	Yes	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	20/01/2024	1	No	No	No
6	01/01/2024	07/11/2023	11	No	No	Yes
6	01/01/2024	27/02/2024	2	No	No	No

[v-tianyich-msft]

Hi @ClemFandango ,

Try below expression:

Last Month = IF([Date Today]>[DateAction]&&ABS(DATEDIFF([Date Today],[DateAction],DAY))<30,"Yes","No")

Two Month = IF([Date Today]>[DateAction]&&ABS(DATEDIFF([Date Today],[DateAction],DAY))>30&&ABS(DATEDIFF([Date Today],[DateAction],DAY))<60,"Yes","No")

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[ClemFandango · Accepted solution]

Thanks for your help @v-tianyich-msft 

This ended up being the complete solution 

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