cancel
Showing results for
Did you mean:
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 - Project Column B - Approval Date Column C - Meeting Month X 21 Febuary 2021 December 2020 Y 22 March 2021 Febraury 2021 Z 23 August 2021 June 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
Community Champion

``````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 ``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

9 REPLIES 9
Community Champion

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``````

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

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Helper I

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

Helper I

sorry here are the photos:

Community Champion
Could you share your pbix file

Regards,
Nandu Krishna

Helper I

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

Community Champion

``````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 ``````

Appreciate with a kudos
🙂

Regards,
Nandu Krishna

Helper I

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 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
Helper I

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

Helper I

Thank you for the solution 🙂

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors