Reply
Member
Posts: 59
Registered: ‎07-07-2017
Accepted Solution

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.


Accepted Solutions
Community Support Team
Posts: 2,654
Registered: ‎02-06-2018

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

View solution in original post

Attachment
Community Support Team
Posts: 2,654
Registered: ‎02-06-2018

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.

 

Capture.PNG

 

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

 

Best Regards,

Cherry

View solution in original post

Attachment

All Replies
AlB Super Contributor
Super Contributor
Posts: 787
Registered: ‎11-12-2018

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

Hi @MariosChr90

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
Posts: 2,654
Registered: ‎02-06-2018

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

Highlighted
Member
Posts: 59
Registered: ‎07-07-2017

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

 

 

 

Member
Posts: 59
Registered: ‎07-07-2017

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

Senior Member
Posts: 377
Registered: ‎04-14-2018

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

[ Edited ]

@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
Posts: 2,654
Registered: ‎02-06-2018

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

Attachment
Member
Posts: 59
Registered: ‎07-07-2017

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

Community Support Team
Posts: 2,654
Registered: ‎02-06-2018

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.

 

Capture.PNG

 

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

 

Best Regards,

Cherry

Attachment