Unique values in a period

[AlejandroPCar]

Hi!

I've been tying it for a while but with no positive results. I need a measure that only counts the companies that are continuosly in all the years that are in a selected period. I need be able to select a fisrt year and a final year and beetwen those years (the period) the measure calculates only the companies that appears in all these years. For example if the first year is 2012 and the final is 2015 only the companies that appears in every single year of the period. So if one companie is in 2012, 2013, but is not in 2014 and then it is in 2015, this companie won't be showed.

So, I've been trying using DATESBEETWEN with CALCULATE with no results. I appreciate your help.

Here is my pbix file: https://1drv.ms/u/s!AtTnrgPUQzQCjCb_Ut1ydMbZyJbk

[v-huizhn-msft · Accepted solution]

Hi @AlejandroPCar,

For your sample table, there must relationship between company table and date table. So you should count number of selected years for each company's. Then compare it to the selected years in slicer(from begin to end), if they are equal, we count the company, otherwise we don't count it. 

Because of your sample data isn't in English, I create my sample table to display the solution. There are two tables: company and date, there are relationship between them.

companydaterelationship
Then create a slicer using between mode, you don't need to create two slicers, one displays start year, another one displays end year. Create a measure to calculate the number of years selected in slicer. 

Selected-years = COUNTX(ALLSELECTED('Date'),'Date'[Year])

Create each company's number of years selected in slicer using the formula.

experienced-year = CALCULATE(COUNT(Company[Year]),USERELATIONSHIP(Company[Year],'Date'[Year])

Check if it's equal to the number of years selected in slicer. Finally, we count the companies whose [experinced years]=[selected]. So we can use the formula below. The first is based on the two measures above. The second is a combined measure, both of them are right.

Result =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                Company,
                Company[Company],
                "experinced years", Company[experienced-year]
            ),
            "selected", 'Date'[Selected-years]
        ),
        [experinced years] = [selected]
    )
)
    + 0


unique-result =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                Company,
                Company[Company],
                "experinced years", CALCULATE (
                    COUNT ( Company[Year] ),
                    USERELATIONSHIP ( Company[Year], 'Date'[Year] )
                )
            ),
            "selected", COUNTX ( ALLSELECTED ( 'Date' ), 'Date'[Year] )
        ),
        [experinced years] = [selected]
    )
)
    + 0

Then create a card to display your result. 



Please download the .pbix file and check more details.

Best Regards,
Angelia

[AlejandroPCar]

@v-huizhn-msft

Thank you very much Angeli it seems that solved my issue, but I'm very busy now so as soon as I have enough time I will see the pbix and verify if I can do it by myself. Thanks again.

[v-huizhn-msft]

Hi @AlejandroPCar,

Got it, please mark the reply as answer if you have resolved your issue.

Best Regards,
Angelia

[AlejandroPCar]

Hi Angelia! @v-huizhn-msft

Sorry about the time for a response. Your solution works perfectly. Well... in fact I did not use all your formulas and neither the full proccess because my situation is a little bit more complex but it helped me to understand the measures and so your help was HUGE. 

Again thanks a lot.