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

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

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

1 ACCEPTED SOLUTION
Helper III

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

6 REPLIES 6
Community Support

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

Best regards,
Community Support Team_ Scott Chang

Helper III

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
Community Support

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.

Helper III

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
Community Support

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.

Helper III

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

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.