Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Active satellites per year

Hi! 

 

I have a list of satellites that looks like this. The first column is the name of the satellite, the second is the date of launch, the third one is the beginning of life and the last one is the expected end of life. 

 

What I want to do is to find a measure that calculates the active satellites each year. For example for 2022 it would have to be (active satellites in 2021 + planned satellites with BoL 2022 - retired satellites with EoL in 2022). And then the result of that would be used for the ones in 2023 and so on. 

 

My idea is to have a slicer with the years and be able to see the active satellites in the selected year in a card visualization.

Any ideas? 

 

Thanks a lot!! 

 

 

Name           Date of Launch   BoL     EoL

ABS-22/6/201420142029
ABS-2A6/15/201620162031
ABS-3A3/2/201520152030
ABS-43/13/200420042019
ABS-69/26/199919992023
ABS-79/4/199919992020
AII-Bravo4/29/202120212026
AII-Charlie1/24/202120212026
AISat-16/30/201420142029
AISSat-17/12/201020102013
AISSat-27/8/201420142017
Al Yah-31/25/201820182031
Need Help
  • Syndicate_Admin's avatar
    Syndicate_Admin
    4 years ago

    Hello, dear please check if this solves the problem, so I understood the assets are those that are with equal or less EoL to this year, that is to say that according to your data if we see the assets in the year 2022 would be 8.

    Ivancito111_0-1637598228093.png

    For this value I made a dynamic slicer without connection to the table with the data and a measure so that it counts depending on what is selected in the Slicer.

    Tabla Slicer:

    SlicerYearEol = 
DISTINCT(
    SELECTCOLUMNS(
        CALENDAR( DATE( MIN('Table'[Eol]),01,01), DATE( MAX('Table'[Eol]),01,01)),
        "year", YEAR([Date])
    )
)

    Measure: 

    Measure = 
Var __Count = 
    COUNTROWS( 
        FILTER('Table',
        'Table'[Eol] >= SELECTEDVALUE(SlicerYearEol[year])
    )
)

RETURN 
__Count

    If this helps you and solves the problem, please accept it as a solution.

    Best regards.

4 Replies

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    Hello, dear please check if this solves the problem, so I understood the assets are those that are with equal or less EoL to this year, that is to say that according to your data if we see the assets in the year 2022 would be 8.

    Ivancito111_0-1637598228093.png

    For this value I made a dynamic slicer without connection to the table with the data and a measure so that it counts depending on what is selected in the Slicer.

    Tabla Slicer:

    SlicerYearEol = 
DISTINCT(
    SELECTCOLUMNS(
        CALENDAR( DATE( MIN('Table'[Eol]),01,01), DATE( MAX('Table'[Eol]),01,01)),
        "year", YEAR([Date])
    )
)

    Measure: 

    Measure = 
Var __Count = 
    COUNTROWS( 
        FILTER('Table',
        'Table'[Eol] >= SELECTEDVALUE(SlicerYearEol[year])
    )
)

RETURN 
__Count

    If this helps you and solves the problem, please accept it as a solution.

    Best regards.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Syndicate_Admin ! 

       

      Thanks for your answer. It seems like it should work but I can't manage to implement it. 

       

      Do you create the dynamic slicer with a new measure? or is it another feature? 

      I had created a table with the years of interest, and then I added that to a slicer visualization. What I don't know is how to insert this slicer selection in the measure you're proposing. I mean in this part

      SELECTEDVALUE(SlicerYearEol[year])

       

      Here is how I calculated the table with the years

      Years = SELECTCOLUMNS (GENERATESERIES ( MIN (Satellites[BoL]), MAX (Satellites[EoL]) ),"Year", [Value])
       
      I feel like I'm close but not just yet
      • Syndicate_Admin's avatar
        Syndicate_Admin
        Icon for Administrator rankAdministrator
        4 years ago

        First I apologize for how little explanatory I was previously, now I will try to explain everything I do so that it can be understood.

        1. Create the table with the years:

        I do this with the need to be able to see the active satellites during that period, for example if we choose to see those of 2022 we should see all those that have an Eol greater than the current year, since as you explained the data those would be the assets, correct me if I am wrong.

        To do this I do the following

        Ivancito111_0-1637605191441.png

        I click on New Table from the Data view, and put the following code

        SlicerYearEol = 
DISTINCT(
    SELECTCOLUMNS(
        CALENDAR( DATE( MIN('Table'[Eol]),01,01), DATE( MAX('Table'[Eol]),01,01)),
        "year", YEAR([Date])
    )
)

        As you can see it throws me the date of the years from the Eol to the Maximum Eol. In this case the minimum Eol is 2013 and the maximum is 2031.

        2. I add a Slicer with the new table (SlicerYearEol):

        Ivancito111_1-1637605348603.png

        3. Create the measure to know the quantity

        I believe this measure

        Measure = 
Var __Count = 
    COUNTROWS( 
        FILTER('Table',
        'Table'[Eol] >= SELECTEDVALUE(SlicerYearEol[year])
    )
)

RETURN 
__Count

        This in a few words counts depending on what is selected in the SlicerYearEol, which as we realized before are every year between the minimum Eol and the maximum Eol.

        4. Try

        Then I simply make use of the Slicer and it works as I explained to you.

        Ivancito111_2-1637605476744.png

        Any questions do not hesitate to comment on this message.

        If this solution applies to your requirement do not forget to accept it as a solution so that everyone has better access to it.

        Best regards