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: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

DAX Formula: Hep to fix Formula to retrve prev month but for Jan/Jul then retrive Dec/Jun instead

‎04-23-2020 01:44 AM

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:

Excel piwer bi example.png

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 2022May 2022
Monday, 31 August 2020July 2020
Sunday, 31 December 2023October 2023
Friday, 8 May 2020April 2020
Monday, 31 August 2020July 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Monday, 31 August 2020July 2020
Thursday, 1 October 2020September 2020
Thursday, 1 October 2020September 2020
Friday, 8 May 2020April 2020
Sunday, 1 November 2020October 2020
Thursday, 1 October 2020September 2020
Friday, 1 May 2020April 2020
Thursday, 30 April 2020March 2020
Monday, 31 August 2020July 2020
Sunday, 31 December 2023October 2023
Friday, 31 December 2021October 2021
Monday, 31 August 2020July 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Friday, 24 July 2020June 2020
Friday, 8 May 2020April 2020
Monday, 31 August 2020July 2020
Friday, 12 June 2020May 2020
Friday, 1 May 2020April 2020
Friday, 22 July 2022May 2022
Tuesday, 31 March 2020February 2020
Friday, 31 December 2021October 2021
Monday, 31 August 2020July 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Friday, 8 May 2020April 2020
Friday, 8 May 2020April 2020
Friday, 24 July 2020June 2020
Tuesday, 31 December 2019October 2019
Friday, 24 July 2020June 2020
Friday, 31 December 2021October 2021
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Friday, 12 June 2020May 2020
Tuesday, 1 September 2020August 2020
Friday, 12 June 2020May 2020
Friday, 1 May 2020April 2020
Friday, 1 May 2020April 2020
Monday, 17 January 2022December 2021
Friday, 12 June 2020May 2020
Saturday, 29 February 2020January 2020
Friday, 24 July 2020June 2020
Monday, 17 January 2022December 2021
Sunday, 31 December 2023October 2023
Friday, 30 October 2020September 2020
Friday, 12 June 2020May 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Thursday, 1 October 2020September 2020
Thursday, 1 October 2020September 2020
Thursday, 30 April 2020March 2020
Thursday, 1 October 2020September 2020
Thursday, 1 October 2020September 2020
Friday, 1 May 2020April 2020
Saturday, 29 February 2020January 2020
Friday, 30 October 2020September 2020
Monday, 31 August 2020July 2020
Friday, 12 June 2020May 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Monday, 17 January 2022December 2021
Monday, 17 January 2022December 2021
Monday, 17 January 2022December 2021
Friday, 12 June 2020May 2020
Friday, 12 June 2020May 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Sunday, 1 November 2020October 2020
Friday, 30 October 2020September 2020
Friday, 30 October 2020September 2020
Friday, 12 June 2020May 2020
Friday, 8 May 2020April 2020
Sunday, 31 December 2023October 2023
Sunday, 31 December 2023October 2023
Friday, 12 June 2020May 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Friday, 8 May 2020April 2020
Monday, 31 August 2020July 2020
Monday, 31 August 2020July 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Thursday, 14 September 2023July 2023
Friday, 30 October 2020September 2020
Friday, 30 October 2020September 2020
Thursday, 30 April 2020March 2020
Friday, 12 June 2020May 2020
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Friday, 30 October 2020September 2020
Friday, 12 June 2020May 2020
Friday, 24 July 2020June 2020
Saturday, 1 July 2023June 2023
Saturday, 29 February 2020January 2020
Friday, 31 December 2021October 2021
Friday, 31 December 2021October 2021
Sunday, 31 December 2023October 2023
Thursday, 30 April 2020March 2020
Friday, 30 October 2020September 2020
Friday, 24 July 2020June 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

Labels:
Message 1 of 2
274 Views
0
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

‎04-23-2020 02:19 AM

@Anonymous , try like

new column =
if( month([Latest Decision Due Date]) in{2,8},
format(date(year([Latest Decision Due Date]),month([latest decision due date])-2,1),"mmm-yy"),
format(date(year([Latest Decision Due Date]),month([latest decision due date])-1,1),"mmm-yy")
)

 

or

new column =
if( month([Latest Decision Due Date]) in{2,8},
format(date(year([Latest Decision Due Date]),month([latest decision due date])-2,1),"mmmm-yyyy"),
format(date(year([Latest Decision Due Date]),month([latest decision due date])-1,1),"mmmm-yyyy")
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

Message 2 of 2
267 Views
1 REPLY 1
amitchandak
Super User
Super User

‎04-23-2020 02:19 AM

@Anonymous , try like

new column =
if( month([Latest Decision Due Date]) in{2,8},
format(date(year([Latest Decision Due Date]),month([latest decision due date])-2,1),"mmm-yy"),
format(date(year([Latest Decision Due Date]),month([latest decision due date])-1,1),"mmm-yy")
)

 

or

new column =
if( month([Latest Decision Due Date]) in{2,8},
format(date(year([Latest Decision Due Date]),month([latest decision due date])-2,1),"mmmm-yyyy"),
format(date(year([Latest Decision Due Date]),month([latest decision due date])-1,1),"mmmm-yyyy")
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 2 of 2
268 Views

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

View All