Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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.

View solution in original post

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.

View solution in original post

8 REPLIES 8
v-piga-msft
Resident Rockstar
Resident Rockstar

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

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

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

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

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.

@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

Anonymous
Not applicable

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

 

 

 

AlB
Super User
Super User

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   

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.