DAX Formula request: Using PREVIOUSMONTH and IF functions to retrive conditional date

[Ankap]

Hi All,

I am trying to create a DAX formula for a Coulmn (Column C - Meeting Month) that retrives the previous month to the value in another column (Column B - Approval date, However there is a caveat that if the value retrived in "Column C - Meeting Month" is January or July then the value should be amended to retrive the month before those - so December and June, as there are no meetings taking place in January or July.

The ideal formula would displayed the following data in the last column (Column C - Meeting Month):

[v-alq-msft]

Hi, @Ankap 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @Ankap 

Based on your description, I created data to reproduce your scenario.

Table:

'Meeting Month' is a calculated column as below.

Meeting Month = VALUE(FORMAT('Table'[Meeting Date],"yyyymm"))

Then you may create two measures as below.

Approval Date = 
var _yearmonth = SELECTEDVALUE('Table'[Meeting Month])
var _lastyearmonth = 
CALCULATE(
    MAX('Table'[Meeting Month]),
    FILTER(
        ALL('Table'),
        'Table'[Meeting Month]<_yearmonth
    )
)
return
IF(
    OR(
    RIGHT(_yearmonth,2) = "01",
    RIGHT(_yearmonth,2) = "07"
    ),
    CALCULATE(
        CONCATENATEX('Table','Table'[Meeting Date],","),
        FILTER(
            ALL('Table'),
            'Table'[Meeting Month] = _lastyearmonth
        )
    ),
    CALCULATE(
        CONCATENATEX('Table','Table'[Meeting Date],","),
        FILTER(
            ALL('Table'),
            'Table'[Meeting Month] = _yearmonth
        )
    )
)

Approval Month = 
var _yearmonth = SELECTEDVALUE('Table'[Meeting Month])
var _lastyearmonth = 
CALCULATE(
    MAX('Table'[Meeting Month]),
    FILTER(
        ALL('Table'),
        'Table'[Meeting Month]<_yearmonth
    )
)
return
IF(
    OR(
    RIGHT(_yearmonth,2) = "01",
    RIGHT(_yearmonth,2) = "07"
    ),
    _lastyearmonth,
    _yearmonth
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ankap , You need join both the dates to common date dimension and use userelation to activate join

Refer: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

With use relation, you can use Time intelligence lik

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))


last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))


MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/