cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ankap
Helper I
Helper I

DAX Formula request: Using PREVIOUSMONTH and IF functions to retrive conditional date

Hi All

 

I am trying to create a DAX formula with the PREVIOUSMONTH and IF functions however do not know how to do it/what it should look like. I am trying to create a column (Column C - Meeting Month) to retrive the previous month based on the inputs of another column (Column B - Approval Date), howver in the instance where "Column C - Meeting Month" would be January or July then the formula should adjust it to read December or June respectively - as no meetings are taking place for approvals in Jan or Jul.

Below is a table which I am hoping to create, Column C - Meeting Month being where the DAX formula will apply to:

 

Column A - ProjectColumn B - Approval DateColumn C - Meeting Month
X21 Febuary 2021December 2020
Y22 March 2021Febraury 2021
Z23 August 2021June 2021

 

I have looked at the basics of using PREVIOUSMONTh and IF functions but am unable to apply them to this scenario.

 

I would appreciate any help as I am new to PowerBi Desktop.

 

Thankyou 🙂
Ank

1 ACCEPTED SOLUTION

@Ankap 

 

 

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

 

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

View solution in original post

9 REPLIES 9
nandukrishnavs
Super User
Super User

@Ankap -

 

Try this calculated column.

 

 

Column C - Meeting Month =
VAR _sldate =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Column B - Approval Date] ) )
VAR _twomonthback =
    EDATE ( _sldate, -2 )
VAR _onemonthback =
    EDATE ( _sldate, -1 )
VAR _isbalnkprev =
    ISBLANK ( PREVIOUSMONTH ( 'Table'[Column B - Approval Date] ) )
VAR _result =
    IF (
        ISBLANK ( PREVIOUSMONTH ( 'Table'[Column B - Approval Date] ) ),
        _twomonthback,
        _onemonthback
    )
RETURN
    _result

 

 

new.JPG

 

Since this is a data preparation activity, you can try M query as well. 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

 

 

Hi there,

 

Sorry to have untaked the solution, Unfortunately when i tried your solution i am gven the following error when i input the below reccomended formula into a calculated column (titles and names being adjusted from the intiial example):

"A table of multiple values was supplied where a single value was expected."

 

sorry here are the photos:
power bi issue formula.pngpower bi issue.png

Could you share your pbix file

Hi Nandu,

 

The system wont let me send more messages...

 

Thanks for bearing with me, I appreciate it. I believe my powerbi is current, i will check. I tried the fromula and got the same error, I have provided my table aswell (IE wasnt allowing me to paste it before):

Latest Decision Due Date Meeting Month

Friday, 22 July 2022#ERROR
Monday, 31 August 2020#ERROR
Sunday, 31 December 2023#ERROR
Friday, 8 May 2020#ERROR
Monday, 31 August 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Monday, 31 August 2020#ERROR
Thursday, 1 October 2020#ERROR
Thursday, 1 October 2020#ERROR
Friday, 8 May 2020#ERROR
Sunday, 1 November 2020#ERROR
Thursday, 1 October 2020#ERROR
Friday, 1 May 2020#ERROR
Thursday, 30 April 2020#ERROR
Monday, 31 August 2020#ERROR
Sunday, 31 December 2023#ERROR
Friday, 31 December 2021#ERROR
Monday, 31 August 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 8 May 2020#ERROR
Monday, 31 August 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 1 May 2020#ERROR
Friday, 22 July 2022#ERROR
Tuesday, 31 March 2020#ERROR
Friday, 31 December 2021#ERROR
Monday, 31 August 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 8 May 2020#ERROR
Friday, 8 May 2020#ERROR
Friday, 24 July 2020#ERROR
Tuesday, 31 December 2019#ERROR
Sunday, 31 December 2023#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Wednesday, 21 December 2022#ERROR
Monday, 17 January 2022#ERROR
Thursday, 1 December 2022#ERROR
Monday, 17 January 2022#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Tuesday, 28 January 2020#ERROR
Friday, 30 April 2021#ERROR
Friday, 24 July 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 8 May 2020#ERROR
Sunday, 31 December 2023#ERROR
Sunday, 31 December 2023#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 8 May 2020#ERROR
Monday, 31 August 2020#ERROR
Monday, 31 August 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Thursday, 14 September 2023#ERROR
Friday, 30 October 2020#ERROR
Friday, 30 October 2020#ERROR
Thursday, 30 April 2020#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 24 July 2020#ERROR
Saturday, 1 July 2023#ERROR
Saturday, 29 February 2020#ERROR
Friday, 31 December 2021#ERROR
Friday, 31 December 2021#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 31 December 2021#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 8 May 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 24 July 2020#ERROR
Sunday, 31 December 2023#ERROR
Saturday, 29 February 2020#ERROR
Tuesday, 31 March 2020#ERROR
Sunday, 31 December 2023#ERROR
Sunday, 31 December 2023#ERROR
Monday, 17 January 2022#ERROR
Saturday, 31 December 2022#ERROR
Friday, 31 December 2021#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
 #ERROR
Saturday, 29 February 2020#ERROR
Tuesday, 31 December 2019#ERROR
Friday, 24 July 2020#ERROR
Tuesday, 31 December 2019#ERROR
Tuesday, 31 December 2019#ERROR
Friday, 24 July 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 8 May 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 24 July 2020#ERROR
Monday, 17 January 2022#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 12 June 2020#ERROR
Friday, 8 May 2020#ERROR
Tuesday, 27 June 2023#ERROR
Saturday, 31 December 2022#ERROR
Tuesday, 27 June 2023#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Friday, 8 May 2020#ERROR
Sunday, 31 December 2023#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Friday, 12 June 2020#ERROR
Sunday, 31 December 2023#ERROR
Friday, 24 July 2020#ERROR
Friday, 24 July 2020#ERROR
Friday, 24 July 2020#ERROR
Sunday, 31 December 2023#ERROR
Thursday, 30 April 2020#ERROR
Friday, 30 October 2020#ERROR
Monday, 17 August 2020#ERROR
Friday, 24 July 2020#ERROR
Thursday, 14 September 2023#ERROR
Tuesday, 31 December 2019#ERROR
Tuesday, 31 December 2019#ERROR

 

Annotation 2020-04-23 145702.png

@Ankap 

 

 

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

 

 



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂

Hi @nandukrishnavs - the below error still applies unfortunatly 

 

So were almost there!! And i will be more than happy to like all your comments and list your solutions as the "solution"

 

The general formula sees to be delivering on its function, 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). I've tried tinkering with the formula on my own (didnt want to annoy you further) but I just break it haha. the below is a sample of my data set.

 

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

Hi Nandu, unfortunately I am not able to (due to restrcitions on onedrive and dropbox placed by my employer)...

Thank you for the solution 🙂

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!