Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data coverage based on fiscal year

Hi 

 

I have a table of companies and the fiscal year in which data is available. I am trying to count the numbers of companies that have data in all of 2016 ,2017 and 2018 on the dashboard. Please advise, thank you!

 

companyidFiscalYear
2221352011
2221352011
2221352011
2221352011
2221352012
2221352012
2221352012
2221352012
2221352012
2221352013
2221352013
2221352013
2221352013
2221352013
2221352013
2221352014
2221352014
2221352014
2221352014
2221352014
2221352014
2221352014
2221352015
2221352015
2221352015
2221352015
2221352015
2221352015
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2221352017
2223272011
2223272011
2223272011
2223272011
2223272012
2223272012
2223272012
2223272012
2223272012
2223272013
2223272013
2223272013
2223272013
2223272013
2223272014
2223272014
2223272014
2223272014
2223272014
2223272015
2223272015
2223272015
2223272015
2223272015
2223272016
2223272016
2223272016
2223272016
2223272016
2223272016
2223272016
2223272017
2223272017
2223272017
2223272017
2223272017
2223272018
2223272018
2223272018
2223272018
2223272018
2223272018
2240552011
2240552011
2240552012
2240552012
2240552013
2240552013
2240552014
2240552014
2240552015
2240552015
2240552016
2240552016
2240552017
2240552017
2240552018
2240552018
2249912011
2249912011
2249912012
2249912012
2249912012
2249912012
2249912012
2249912013
2249912013
2249912013
2249912013
2249912013
2249912014
2249912014
2249912014
2249912014
2249912014
2249912015
2249912015
2249912015
2249912015
2249912015
2249912016
2249912016
2249912016
2249912016
2249912016
2249912017
2249912017
2249912017
2249912017
2249912017
2249912018
2249912018
2249912018
2249912018
2249912018
2283992011
2283992012
2283992013
2283992014
2283992015
2283992016
2283992017
2283992018
2285912011
2285912011
2285912011
2285912011
2285912011
2285912011
2285912012
2285912012
2285912012
2285912012
2285912012
2285912012
2285912013
2285912013
2285912013
2285912013
2285912013
2285912013
2285912014
2285912014
2285912014
2285912014
2285912014
2285912015
2285912015
2285912015
2285912015
2285912015
2285912015
2285912016
2285912016
2285912016
2285912016
2285912016
2285912017
2285912017
2285912017
2285912017
2285912017
2285912018
2285912018
2285912018
2285912018
2285912018
2285912018
2285912018
2285912018
2285912018
Need Help
Show and Tell
  • mwegener's avatar
    mwegener
    6 years ago

    Hi Anonymous 

     

    try this

    Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER ( ALL ( 'Table' ), 'Table'[FiscalYear] IN { 2016, 2017, 2018 } ),
            'Table'[companyid],
            "@Cnt", DISTINCTCOUNT ( 'Table'[FiscalYear] )
        ),
        [@Cnt] = 3
    )
)

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

5 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi Anonymous ,

     

    try this.

     

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    mwegener This is not what I'm looking for actually. I am looking for companies that have all years of data for 2016, 2017 and 2018. In my example, there are 5 companies that fit such scenario, but I don't know how to calculate systemically. By Distinct Count, it only shows me data coverage of each year, and the total means number of companies that have data in at least one year, but I want to count companies that have data in all of the years.

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      Hi Anonymous 

       

      try this

      Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            FILTER ( ALL ( 'Table' ), 'Table'[FiscalYear] IN { 2016, 2017, 2018 } ),
            'Table'[companyid],
            "@Cnt", DISTINCTCOUNT ( 'Table'[FiscalYear] )
        ),
        [@Cnt] = 3
    )
)

      Regards,

      Marcus

      Dortmund - Germany
      If I answered your question, please mark my post as solution, this will also help others.
      Please give Kudos for support.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Great! This works perfectly. Thank you!

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      hi Anonymous 

      Just use this logic to create a measure

      Measure 4 = 
SUMX (
    VALUES ( 'Table'[companyid] ),
    IF (
        2016 IN CALCULATETABLE ( VALUES ( 'Table'[FiscalYear] ) )
        && 2017 IN CALCULATETABLE ( VALUES ( 'Table'[FiscalYear] ) )
        && 2018 IN CALCULATETABLE ( VALUES ( 'Table'[FiscalYear] ) ),
        1,
        0
    )
)

      Result:

      Regards,

      Lin