Hi All,
I am trying to create a caluclated column that would show a meeting taking place a month beore each date in the original column, the condition being if the meeting would fall in January or July then the 2nd columns Meeting date should instead reflect is occuring in December or June respecively. it would look like the following:
I have tried creating it my self with the following formula but got the following error:
I would appreciate if anyone has a work around or a fix. Note de to company restricitions i cannot share the file.
Thank you
Ank
Hi @Ankap ,
If you want to get previous month in calculated column and ignore Jan and July, you could refer to below expression
Column = if( MONTH('Table'[Latest Decision Due Date]) in {12,7} , DATE(YEAR('Table'[Latest Decision Due Date]), MONTH('Table'[Latest Decision Due Date])-2,1), DATE(YEAR('Table'[Latest Decision Due Date]), MONTH('Table'[Latest Decision Due Date])-1,1))But I find that some records in your table can't use above expression
If possible , could you please explain this to me in details? Then I will help you more correctly.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
Note: if you are dealing with a larger dataset, you may face performance issues.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
Regards,
Nandu Krishna
Hi @Ankap ,
If you want to get previous month in calculated column and ignore Jan and July, you could refer to below expression
Column = if( MONTH('Table'[Latest Decision Due Date]) in {12,7} , DATE(YEAR('Table'[Latest Decision Due Date]), MONTH('Table'[Latest Decision Due Date])-2,1), DATE(YEAR('Table'[Latest Decision Due Date]), MONTH('Table'[Latest Decision Due Date])-1,1))But I find that some records in your table can't use above expression
If possible , could you please explain this to me in details? Then I will help you more correctly.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Ankap - I was confused with your previous post. Please ignore my above comment.
Regards,
Nandu Krishna
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
