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: 
Reply
Ankap
Helper I
Helper I

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

‎04-20-2020 08:38 PM

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

Labels:
Message 1 of 10
766 Views
0
1 ACCEPTED SOLUTION
nandukrishnavs
Community Champion
Community Champion
In response to Ankap

‎04-22-2020 10:23 PM

@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

Message 9 of 10
649 Views
9 REPLIES 9
nandukrishnavs
Community Champion
Community Champion

‎04-20-2020 10:51 PM

@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 🙂

 

 

Message 2 of 10
743 Views
Ankap
Helper I
Helper I
In response to nandukrishnavs

‎04-22-2020 07:11 PM

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."

 
Message 4 of 10
690 Views
0
Ankap
Helper I
Helper I
In response to Ankap

‎04-22-2020 07:16 PM

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

Message 5 of 10
688 Views
0
nandukrishnavs
Community Champion
Community Champion
In response to Ankap

‎04-22-2020 08:00 PM
Could you share your pbix file
Message 6 of 10
678 Views
0
Ankap
Helper I
Helper I
In response to nandukrishnavs

‎04-22-2020 10:10 PM

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

Message 8 of 10
653 Views
0
nandukrishnavs
Community Champion
Community Champion
In response to Ankap

‎04-22-2020 10:23 PM

@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 🙂

Message 9 of 10
650 Views
Ankap
Helper I
Helper I
In response to nandukrishnavs

‎04-22-2020 11:37 PM

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
Message 10 of 10
638 Views
0
Ankap
Helper I
Helper I
In response to nandukrishnavs

‎04-22-2020 08:34 PM

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

Message 7 of 10
672 Views
0
Ankap
Helper I
Helper I
In response to nandukrishnavs

‎04-20-2020 11:31 PM

Thank you for the solution 🙂

Message 3 of 10
727 Views
0

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

View All
Top Solution Authors
View All