Active satellites per year

[Anonymous]

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-2	2/6/2014	2014	2029
ABS-2A	6/15/2016	2016	2031
ABS-3A	3/2/2015	2015	2030
ABS-4	3/13/2004	2004	2019
ABS-6	9/26/1999	1999	2023
ABS-7	9/4/1999	1999	2020
AII-Bravo	4/29/2021	2021	2026
AII-Charlie	1/24/2021	2021	2026
AISat-1	6/30/2014	2014	2029
AISSat-1	7/12/2010	2010	2013
AISSat-2	7/8/2014	2014	2017
Al Yah-3	1/25/2018	2018	2031

[Syndicate_Admin · Accepted solution]

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.

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.

[Syndicate_Admin]

Nevermind @Ivancito111 I got it!. Your measure works perfectly!

[Anonymous]

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]

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

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):

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.

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