Search code examples
powerbireportdax

Getting equivalent min percentage year

I have the data bellow that shows year, value and cumulative percentage

create table #data (cost money, Current_Year int,   Cumulative money, [Percentage]  decimal(9,4))
insert into #data values

(43000000,2014,43000000,0.000647922726779209),
(54000000,2015,97000000,0.00146159312785077),
(60777777.75,2016,157777777.75,0.00237739088337079),
(123777777.8,2017,281555555.55,0.0042424707742277),
(126555555.5,2018,408111111.05,0.00614940613721881),
(60761257937,2019,61169369048.05,0.921698241604101),
(492914890.8,2020,61662283938.85,0.929125468583571),
(456692522.2,2021,62118976461.05,0.936006897985512),
(416673457.6,2022,62535649918.65,0.942285321307017),
(422831144,2023,62958481062.65,0.94865652846487),
(371598857.2,2024,63330079919.85,0.954255769042197),
(346364399,2025,63676444318.85,0.959474777550553),
(336826238.7,2026,64013270557.55,0.964550065341932),
(350483170.1,2027,64363753727.65,0.969831135371267),
(334551349.4,2028,64698305077.05,0.974872145198032),
(310619368.1,2029,65008924445.15,0.97955254863919),
(344523398.6,2030,65353447843.75,0.984743817008031),
(345189362.5,2031,65698637206.25,0.989945120101198),
(332918789.2,2032,66031555995.45,0.994961530559209),
(334382754.3,2033,66365938749.75,1)
select * from #data
drop table #data

I want to be able to select a percentage in my case between 94% and 95% in my filter to return the equivalent min year for that that percentage and the percentage I tried

95%Range = var result =IF([Cost constant %]>=0.94  && [Cost constant %]<=0.95,[Cost constant %],BLANK())
           return
         IF(result <>BLANK(),MAX(Calendar_Date[Current_Year])

it is not working

expected output

enter image description here

        )
Solution

  • I was able to do these using two measures:

    Min Year = min(Query1[Current_Year])

Percentage Lookup = LOOKUPVALUE(Query1[Percentage],Query1[Current_Year],[Min Year])

    You can then add percentage to a filter or slicer and Power BI will return the earliest year that matches the filter criteria. Here are the results in a simple table visual:

    result