Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ClemFandango
Helper III
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

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

 

View solution in original post

6 REPLIES 6
v-tianyich-msft
Community Support
Community Support

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

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

Hi @ClemFandango ,

 

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

vtianyichmsft_0-1720593162131.png

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.

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

Hi @ClemFandango ,

 

Try below expression:

vtianyichmsft_1-1720599026070.png

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.

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors