Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |