How to get a Date column value to be used by measures

DAX DAX Calculations
1

I want to evaluate a date field (ContractStartDate) for every row in one of my reference tables to see IF it falls within the date range of the report context. When I go to create a Measure, I can’t even get to this column name. Do I have to preface it with a date function first? I tried this and still can’t get to the column name. What am I missing?

2

Thanks for posting your question @kjssdca. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

3

@kjssdca

Try wrapping ContractStartDate with SELECTEDVALUE function.

Thanks.

4

@kjssdca Hey ,

You can refer this post PB Community
https://community.powerbi.com/t5/Desktop/Check-if-date-falls-between-two-dates/td-p/1149275

I hope this will work for you .
Dax Function. and you can day count as well currently using 2 week time interval .

Within Two Weeks =
VAR VendorDate =
    MAX( Vendors[Last Contacted Date] )
VAR SelectedDates =
    ALLSELECTED( 'Date'[Date] )
VAR SelectedDate = [Selected Dates]
VAR DayCount = 14
VAR DateRange =
    DATESBETWEEN(
        'Date'[Date],
        SelectedDate - DayCount,
        SelectedDate
    )
VAR WithinDateRange = VendorDate
    IN DateRange
VAR Result =
    IF(
        HASONEVALUE( 'Date'[Date] ),
        WithinDateRange,
        "Multiple Selections"
    )
RETURN
    Result
2 Likes
5

Thank you @samharishk The thing is - the date field I want to check is in a reference table, and so there isn’t a relationship between it and the Dates table, (which is another reference table, of course). Maybe that’s where things are breaking down but I thought it was not good practice to create relationships between the reference tables.

6

HI @kjssdca. I think a PBIX that illustrated your issue would be the next step.

To help the forum members further analyze your current state and visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Also, if you provide DAX in your post, please format it using the built-in formatter.

Greg
_eDNA Forum - Format DAX

7

Hi @kjssdca, we’ve noticed that no response has been received from you since the 14th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!