cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Super User

@Anonymous

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

@Anonymous -

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

Anonymous
Not applicable

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

Anonymous
Not applicable

sorry here are the photos:

Super User
Could you share your pbix file

Regards,
Nandu Krishna

Anonymous
Not applicable

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

Super User

@Anonymous

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

Anonymous
Not applicable

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
Anonymous
Not applicable

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

Anonymous
Not applicable

Thank you for the solution 🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.