Request: DAX Formula help for previous month calcuation with condition to skip months of Jan and Jul

[Ankap]

Hi All,

I am trying to create a caluclated column that would show a meeting taking place a month beore each date in the original column, the condition being if the meeting would fall in January or July then the 2nd columns Meeting date should instead reflect is occuring in December or June respecively. it would look like the following:

The general formula sees to be delivering on its function, to recall the month before. However I have noticed some discrepencies in the data - namely the formula is still not skipping January and July month meetings, instead showing meetings to occur then (instead of marking them as December and June month meetings as it should function). Additionally December decision dates are showing October month meetings (instead of November). This is the formula i am using:

Meeting Month = 
VAR _sldate =
    CALCULATE ( SELECTEDVALUE ( 'Decisions Register (2)'[Latest Decision Due Date] ) )
VAR _twomonthback =
    EDATE ( _sldate, -2 )
VAR _onemonthback =
    EDATE ( _sldate, -1 )
VAR _p1 =
    EOMONTH ( 'Decisions Register (2)'[Latest Decision Due Date], -1 )
VAR _p2 =
    EOMONTH ( 'Decisions Register (2)'[Latest Decision Due Date], -2 )
VAR _isbalnkprev =
    ISBLANK (
        COUNTROWS (
            FILTER (
                'Decisions Register (2)',
                'Decisions Register (2)'[Latest Decision Due Date] > _p2
                    && 'Decisions Register (2)'[Latest Decision Due Date] <= _p1
            )
        )
    )
VAR _result =
    IF (  _isbalnkprev,_twomonthback,_onemonthback )
RETURN
    _result 

Here is a sample of the table that is generated, note the aforementioned skipping of November skipping of January and July month meetings, instead showing meetings to occur then (instead of marking them as December and June month meetings as it should function). Additionally December decision dates are showing October month meetings (instead of November):

Latest Decision Due Date	Meeting Month
Friday, 22 July 2022	May 2022
Monday, 31 August 2020	July 2020
Sunday, 31 December 2023	October 2023
Friday, 8 May 2020	April 2020
Monday, 31 August 2020	July 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Monday, 31 August 2020	July 2020
Thursday, 1 October 2020	September 2020
Thursday, 1 October 2020	September 2020
Friday, 8 May 2020	April 2020
Sunday, 1 November 2020	October 2020
Thursday, 1 October 2020	September 2020
Friday, 1 May 2020	April 2020
Thursday, 30 April 2020	March 2020
Monday, 31 August 2020	July 2020
Sunday, 31 December 2023	October 2023
Friday, 31 December 2021	October 2021
Monday, 31 August 2020	July 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Friday, 24 July 2020	June 2020
Friday, 8 May 2020	April 2020
Monday, 31 August 2020	July 2020
Friday, 12 June 2020	May 2020
Friday, 1 May 2020	April 2020
Friday, 22 July 2022	May 2022
Tuesday, 31 March 2020	February 2020
Friday, 31 December 2021	October 2021
Monday, 31 August 2020	July 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Friday, 8 May 2020	April 2020
Friday, 8 May 2020	April 2020
Friday, 24 July 2020	June 2020
Tuesday, 31 December 2019	October 2019
Friday, 24 July 2020	June 2020
Friday, 31 December 2021	October 2021
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Friday, 12 June 2020	May 2020
Tuesday, 1 September 2020	August 2020
Friday, 12 June 2020	May 2020
Friday, 1 May 2020	April 2020
Friday, 1 May 2020	April 2020
Monday, 17 January 2022	December 2021
Friday, 12 June 2020	May 2020
Saturday, 29 February 2020	January 2020
Friday, 24 July 2020	June 2020
Monday, 17 January 2022	December 2021
Sunday, 31 December 2023	October 2023
Friday, 30 October 2020	September 2020
Friday, 12 June 2020	May 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Thursday, 1 October 2020	September 2020
Thursday, 1 October 2020	September 2020
Thursday, 30 April 2020	March 2020
Thursday, 1 October 2020	September 2020
Thursday, 1 October 2020	September 2020
Friday, 1 May 2020	April 2020
Saturday, 29 February 2020	January 2020
Friday, 30 October 2020	September 2020
Monday, 31 August 2020	July 2020
Friday, 12 June 2020	May 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Monday, 17 January 2022	December 2021
Monday, 17 January 2022	December 2021
Monday, 17 January 2022	December 2021
Friday, 12 June 2020	May 2020
Friday, 12 June 2020	May 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Sunday, 1 November 2020	October 2020
Friday, 30 October 2020	September 2020
Friday, 30 October 2020	September 2020
Friday, 12 June 2020	May 2020
Friday, 8 May 2020	April 2020
Sunday, 31 December 2023	October 2023
Sunday, 31 December 2023	October 2023
Friday, 12 June 2020	May 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Friday, 8 May 2020	April 2020
Monday, 31 August 2020	July 2020
Monday, 31 August 2020	July 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Thursday, 14 September 2023	July 2023
Friday, 30 October 2020	September 2020
Friday, 30 October 2020	September 2020
Thursday, 30 April 2020	March 2020
Friday, 12 June 2020	May 2020
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Friday, 30 October 2020	September 2020
Friday, 12 June 2020	May 2020
Friday, 24 July 2020	June 2020
Saturday, 1 July 2023	June 2023
Saturday, 29 February 2020	January 2020
Friday, 31 December 2021	October 2021
Friday, 31 December 2021	October 2021
Sunday, 31 December 2023	October 2023
Thursday, 30 April 2020	March 2020
Friday, 30 October 2020	September 2020
Friday, 24 July 2020	June 2020

I have tried creating tinkering with the formula but it has not worked. I would appreciate any assistance as I am a beginner.

Thank you for your time!!
Ankit

[Anonymous · Accepted solution]

Here's the column:

Meeting Month = 
    var __currentDate = 'Dates'[Latest Decision Due Date]
    var __currentYear = YEAR( __currentDate )
    var __currentDateMonth = MONTH( __currentDate )
    var __oneMonthBefore = MOD( __currentDateMonth - 2, 12 ) + 1
    var __adjustedOneMonthBefore =
        IF( __oneMonthBefore in {1,7},
            MOD( __oneMonthBefore - 2, 12 ) + 1,
            __oneMonthBefore
        )
    return
        if( __adjustedOneMonthBefore = 12,
            date( __currentYear - 1, __adjustedOneMonthBefore, 1),
            date( __currentYear, __adjustedOneMonthBefore, 1)
        )

MOD returns the remainder (modulo) of the integer division of a and b. If you want to move back one month, you have to move from 1 to 12 and this formula does it: MOD( 1 - 2, 12 ) + 1 = 11 + 1 = 12. MOD( m - 2, 12 ) + 1 = m - 1 for  1 < m <= 12.

Best

D