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
Solved! Go to Solution.
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 🙂
@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
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:
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 |
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 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 |
Hi Nandu, unfortunately I am not able to (due to restrcitions on onedrive and dropbox placed by my employer)...
Thank you for the solution 🙂
User | Count |
---|---|
94 | |
38 | |
35 | |
15 | |
14 |
User | Count |
---|---|
99 | |
30 | |
28 | |
17 | |
15 |