cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
Community Support Team

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

Hi @Anonymous,

 

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.
Highlighted
Community Support Team
Community Support Team

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

Hi @Anonymous,

 

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

 

Capture.PNG

 

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
Super User
Super User

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

Hi @Anonymous

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
Community Support Team

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

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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. 

  1.PNG                                                     

 

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

2.PNG

 

 

 

Anonymous
Not applicable

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. 

  1.PNG                                                     

 

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

2.PNG

themistoklis New Contributor
New Contributor

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

@Anonymous

 

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
Community Support Team

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

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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.

 

Capture.PNG

Highlighted
Community Support Team
Community Support Team

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

Hi @Anonymous,

 

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

 

Capture.PNG

 

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.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 134 members 1,892 guests
Please welcome our newest community members: