Forum Discussion
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
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
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
9 Replies
- nandukrishnavs
Community Champion
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 _resultSince 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."