Determine if a date belongs to a specific date range

[Anonymous]

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.

[v-piga-msft · Accepted solution]

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

[v-piga-msft]

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

[Anonymous]

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

[v-piga-msft · Accepted solution]

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

[Anonymous]

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.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

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

[themistoklis]

@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]

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

[AlB]

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