Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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

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

‎06-27-2024 05:05 AM

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    MonthofDateActionExclude    Last Month    2months ago    
127/08/202425/05/20245NoYesNo
127/08/202426/07/20247YesNoNo
227/08/202401/03/20243NoNoNo
427/08/202401/04/20244NoNoYes

 

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

ID    Date Today    DateAction    MonthofDateActionExclude    Last Month    2months ago    
127/05/202425/05/20245NoNoNo
127/05/202426/07/20247YesNoNo
227/05/202401/03/20243NoNoYes
427/05/202401/04/20244NoYesNo

 

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

 

Many thanks,

 

CF

Message 1 of 11
465 Views
0
1 ACCEPTED SOLUTION
v-yaningy-msft
Community Support
Community Support

‎06-27-2024 07:52 PM

Hi, @ClemFandango 

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

vyaningymsft_0-1719543055509.png

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

 

View solution in original post

lastMonth.pbix
Message 4 of 11
414 Views
10 REPLIES 10
v-yaningy-msft
Community Support
Community Support

‎06-27-2024 07:52 PM

Hi, @ClemFandango 

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

vyaningymsft_0-1719543055509.png

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

 

lastMonth.pbix
Message 4 of 11
415 Views
ClemFandango
Helper III
Helper III
In response to v-yaningy-msft

‎07-07-2024 11:53 PM

Hi @v-yaningy-msft 

 

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

Message 6 of 11
314 Views
0
v-yaningy-msft
Community Support
Community Support
In response to ClemFandango

‎07-08-2024 12:05 AM

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

Message 7 of 11
311 Views
ClemFandango
Helper III
Helper III
In response to v-yaningy-msft

‎07-08-2024 01:42 AM

Hi @v-yaningy-msft 

 

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    MonthofDateActionExclude    Last Month    2months ago    
127/08/202425/05/20245NoYesNo
127/08/202426/07/20247YesNoNo
227/08/202401/03/20243NoNoNo
427/08/202401/04/20244NoNoYes
527/08/202425/05/20235NoNoNo
527/08/202425/05/20245NoYesNo

 

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    MonthofDateActionExclude    Last Month    2months ago    
127/05/202425/05/20245NoNoNo
127/05/202426/07/20247YesNoNo
227/05/202401/03/20243NoNoYes
427/05/202401/04/20244NoYesNo
527/05/202401/03/20233NoNoNo
527/05/202401/04/20234NoNoNo
527/05/202401/04/20247NoYesNo
527/05/202401/03/20247NoNoYes

 

Hope this makes sense

 

Many thanks,

 

CF

Message 8 of 11
301 Views
0
v-yaningy-msft
Community Support
Community Support
In response to ClemFandango

‎07-08-2024 02:03 AM

Hi, @ClemFandango 

Adding a year level filter to each dax will work.

vyaningymsft_0-1720429230397.png

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

lastMonth.pbix
Message 9 of 11
286 Views
ClemFandango
Helper III
Helper III
In response to v-yaningy-msft

‎07-10-2024 06:30 AM

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

 

Message 11 of 11
232 Views
0
ClemFandango
Helper III
Helper III
In response to v-yaningy-msft

‎07-08-2024 04:25 AM

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    MonthofDateActionExclude    Last Month    2months ago    
101/01/202425/05/20245NoNoNo
101/01/202426/07/20247YesNoNo
201/01/202401/03/20243NoNoNo
401/01/202401/04/20244NoNoNo
501/01/202425/05/20235NoNoNo
501/01/202425/05/20245NoNoNo
601/01/202405/12/202312NoYesNo
601/01/202407/11/202311NoNoYes

 

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

Message 10 of 11
278 Views
0
ClemFandango
Helper III
Helper III
In response to v-yaningy-msft

‎06-28-2024 06:43 AM

This is amazing @v-yaningy-msft 

 

Huge thanks!

Message 5 of 11
402 Views
0
lbendlin
Super User
Super User

‎06-27-2024 06:08 PM

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

Message 2 of 11
419 Views
0
ClemFandango
Helper III
Helper III
In response to lbendlin

‎06-28-2024 06:44 AM

Hi @lbendlin 

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

Message 3 of 11
401 Views
0

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

View All