Forum Discussion

EugenioProlog's avatar
EugenioProlog
Helper III
1 year ago

Filtering Active Contracts Over a Period

I have a table in Power BI called all_contracts, which contains contract values over multiple years.


I need to create a dashboard that allows me to filter by a start year and retrieve all companies that had active contracts in that year. Then, I select an end year, and the dashboard should show the total contract value of these companies over the years between the selected start and end years.

How can I structure this in Power BI to ensure accurate filtering and visualization? Any suggestions or best practices?

Below is attached my pbix file with my data.
https://drive.google.com/file/d/1EMd06M1fD3v0HVn8ecCsyADXLsZGl6a_/view?usp=sharing 

Need Help

2 Replies

  • freginier's avatar
    freginier
    Solution Sage
    1 year ago

    Hey there!

     

    Since the dataset contains contracts for multiple years, we need a year filter table.

     

    Years = DISTINCT(YEAR(all_contracts[Date])) (This table will allow users to select a start year and end year.)

    Then add two slicers using the Year table: One for Start Year. One for End Year.

     

    Now, define a measure that ensures only contracts within the selected range are displayed using DAX: 

    Filtered Contracts =
    VAR StartYear = SELECTEDVALUE(Years[Year])
    VAR EndYear = SELECTEDVALUE(Years[Year], MAX(all_contracts[Date]))

    RETURN
    CALCULATE(
    SUM(all_contracts[contract_value]),
    FILTER(
    all_contracts,
    YEAR(all_contracts[Date]) >= StartYear &&
    YEAR(all_contracts[Date]) <= EndYear
    )
    )

     

    fianlly, add the Measure to a Table or Visualization (Use the Filtered Contracts measure in a table, card, or chart.
    The measure dynamically updates based on the selected start and end years.) 

     

    Hope this helps! 

    😁😁

  • EugenioProlog's avatar
    EugenioProlog
    Helper III
    1 year ago

    I can't seem to create a table with that command. 

    I get this error:

    A single value for column 'Date' in table 'all_contracts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.