Using a date measure for comparison in a column expression

[sanimesa]

I need to filter a table and show only those rows which have a due date less than a given date. 

I have a measure for calculating the given date:

Last Day of Month = CALCULATE(MAX('Calendar 445'[Date].[Date]), FILTER('Calendar 445', [Period No] = MONTH(NOW())), FILTER('Calendar 445', [Year] = YEAR(NOW())) )

I added a column in the table to be filtered called Due in Current Period (yields 1 for satisfying the criteria, 0 for not):

Due In Current Period = IF(ORDER_LINES[ITEM_PROMISE_DT].[Date] <= [Last Day of Month], 1, 0)  

This did not work. 

Then, instead of using a measure, I created a Calculated column with exactly the same expression:

Last Day of Month 2 = CALCULATE(MAX('Calendar 445'[Date].[Date]), FILTER('Calendar 445', [Period No] = MONTH(NOW())), FILTER('Calendar 445', [Year] = YEAR(NOW())))

Changed the formula for due in current month as follows:

Due In Current Period = IF(ORDER_LINES[ITEM_PROMISE_DT].[Date] <= BCI_ORDER_LINES[Last Day of Month 2].[Date], 1, 0)  

This worked as expected.

Can one use a measure in a comparison for a calculated column? Also, if there are better approach to this, appreciate if you can share it. 

[Anonymous · Accepted solution]

Hi @sanimesa,

You can use measure in calculated column, since calculate column calculate based on table contents, so measure result will be fixed.(normally, measure result can be affected by filter/slicer)

Currently, it is impossible to use measure to create dynamic calculate column based on filter/slicer.

In addition, you can also take a look at below link to know more about difference:

Calculated Columns and Measures in DAX

Regards,

Xiaoxin Sheng