cancel
Showing results for
Did you mean:
Member

## Determine if a date belongs to a specific date range

Hello I am following up in a new thread the thred in the link below.

https://community.powerbi.com/t5/Desktop/Determine-if-date-is-between-2-dates/m-p/209365#M92340

I am trying to apply the formula below:

```Column =
CALCULATE (
VALUES ( Quarters[Value] ),
FILTER (
'Quarters',
'Quarters'[Start] <= EARLIER ( 'Calendar'[Date] )
&& 'Quarters'[End] >= EARLIER ( 'Calendar'[Date] )
)
)```

Everything works fine until the function EARLIER. Just after the EARLIER function when I am trying to type the date field from my calendar table I don't have the list of the available data fields to choose it. Can you please support with this?

Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team

## Re: Determine if a date belongs to a specific date range

Hi @MariosChr90,

By my tests, the formula from AlB is also helpful, you could have a try with it.

In addition, from your image, it seems that your formula has another mistake, you missed a ")" after Values("SPECS","SPECS"[Columnname]); please correct it and try again.

Here is my attached test file which reproduces your scenario but I have no issue.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Determine if a date belongs to a specific date range

Hi @MariosChr90,

Here is reference that you could use UNION function to achieve append with Dax.

More details, please refer to the attachment which incluing the detail steps.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Highlighted
Super User

## Re: Determine if a date belongs to a specific date range

probably because you're filtering the Quarters table and there you cannot reference a column on the Calendar table. What row of that table are you referring to? None.  So the engine cannot determine a value and therefore it is trying to avoid that you write it.

What are you trying to do exactly with Calendar[Date]? Maybe if you explain that we can help

Community Support Team

## Re: Determine if a date belongs to a specific date range

Hi @MariosChr90,

By my tests with that formula, it works as expected.

Could you share your data model and the image of the fomula so that I could understand your scenario better.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Determine if a date belongs to a specific date range

Hello,

Please find below the two tables I have. Here I am trying to match the date in "Date" table with the appropriate date range in the SPECS table (the same as the quarters table I mentioned before) and get the related outcome depending on the match.

In my formula I can only choose from the SPECS table and not from the DATE table, so I cannot choose the data field date to compare it with Time Start field.

The formula I have is the below

Member

## Re: Determine if a date belongs to a specific date range

Hello,

Please find below the two tables I have. Here I am trying to match the date in "Date" table with the appropriate date range in the SPECS table (the same as the quarters table I mentioned before) and get the related outcome depending on the match.

In my formula I can only choose from the SPECS table and not from the DATE table, so I cannot choose the data field date to compare it with Time Start field.

The formula I have is the below

New Contributor

## Re: Determine if a date belongs to a specific date range

@MariosChr90

Try the following:

```Column =
CALCULATE (
VALUES ( Specs[BU_LOW] ),
FILTER (
'Specs',
'Date'[Date] >=  'Specs'[Time Start]
&& 'Date'[Date] <= 'Specs'[Time Ended]
)
)```

You also need to check what you put on Values function> Is value field the correct one to put there

Community Support Team

## Re: Determine if a date belongs to a specific date range

Hi @MariosChr90,

By my tests, the formula from AlB is also helpful, you could have a try with it.

In addition, from your image, it seems that your formula has another mistake, you missed a ")" after Values("SPECS","SPECS"[Columnname]); please correct it and try again.

Here is my attached test file which reproduces your scenario but I have no issue.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Determine if a date belongs to a specific date range

Mant thanks @v-piga-msft,

Now it seems to work! I have another question for you before I close this thread. Now that I have applied all my formulas created in DAX in my report. I want to append all my tables into one table. The problem is that now the calculated fields do not appear in the consolidated table after this append. How can I bring all my calculated fields in the appended table? Please see below an example image of what I want to do.

Community Support Team

## Re: Determine if a date belongs to a specific date range

Hi @MariosChr90,

Here is reference that you could use UNION function to achieve append with Dax.

More details, please refer to the attachment which incluing the detail steps.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.