Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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):

Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    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.

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    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.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , 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-trend/ba-p/882970

     

    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/