Forum Discussion

Waxy's avatar
Waxy
Frequent Visitor
5 years ago

Determine available contracts

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')
Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Waxy
      Frequent Visitor
      5 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.