Determine available contracts

[Waxy]

Hello

I have a Contract Assets table that the following  columns ;

• Contract_ID
• AssetName 
• StartDate_Copy - Contract's start date
• EndDate_Copy - Contract's end date

I also have a Dates, date table.

In PBI Desktop, I want to have a data slicer to obtain the start and end dates in question, from these inputs, I would like the table displaying the above fields, plus a field denoting "Available" or "UnAvailable". 

I'm attempting to create a measure that will contain the availability.

Below,  is the code that I am working with... I am really new to Power BI and Dax. I was thinking I could add the Available Measure to the table visual. I keep hitting blocks with the below syntax. I was having a challenge with the brackets...

Available Measure =

Var MinValue = Min(Dates[Date])

Var MaxValue = Max(Dates[Date])

Var ContractStart = SUM(ContractAssets[StartDate_Copy])

Var ContractEnd = Sum(ContractAssets[EndDate_Copy])

Return

if((ContractStart >= MinValue) and (ContractStart <= MaxValue)) or

(ContractEnd >= MinValue and ContractEnd <= MaxValue) or (ContractStart <= MinValue and ContractEnd >= MaxValue),'Available', 'Not Available')

[Anonymous]

Hi  @Waxy  ,

You can change the formula to try like this:

Available Measure =
Var MinValue = Min(Dates[Date])
Var MaxValue = Max(Dates[Date])
Var ContractStart = SUM(ContractAssets[StartDate_Copy])
Var ContractEnd = Sum(ContractAssets[EndDate_Copy])
Return
switch(
       true(),
       (ContractStart >= MinValue) and (ContractStart <= MaxValue),'Available',
       (ContractEnd >= MinValue) and (ContractEnd <= MaxValue),'Available',
       (ContractStart <= MinValue) and (ContractEnd >= MaxValue),'Available',
       'Not Available'
)

If my answer is not what you need, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Waxy not sure if it is a typo or you are actually using SUM, it should be MIN or MAX

Var ContractStart = SUM(ContractAssets[StartDate_Copy])
Var ContractEnd = Sum(ContractAssets[EndDate_Copy])

[Waxy]

Was having so much grief with this, starting to get confused how to reference a column in a table. StartDate_Copy and EndDate_Copy are the actual columns in the dataset table. Wasn't having much luck so tried to use variables to capture each row. Guess I'm off track?