contagem de anos consecutivos em DAX

[gelsonwirtijr]

Hello, everyone
I have the following table:

D2_DOCD2_CLIENTED2_CODF2_EMISSAOAnoEmissao

000015387	000133	SINAL SF2	08/02/2017	2017
000018277	000133	SINAL SF2	22/03/2018	2018
000027346	000133	SINAL SF2	10/11/2020	2020
000031179	000133	SINAL SF2	23/11/2021	2021
000031840	000133	SINAL SF3	28/02/2022	2022
000035941	000133	SINAL SF3	17/04/2023	2023

I would like to count the number of consecutive years starting from the current year (2023), for example, in the data set the DAX measure should return the value = 4, referring to the years 2023, 2022, 2021 and 2020.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

in DAX

https://1drv.ms/u/s!AiUZ0Ws7G26RiwCcmmoRwLm6ssTo?e=aS3xJl

[lbendlin]

Why should that be a DAX measure?  How can this be impacted by user actions?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc89CsMwDAXgqxTPAes9ObUydgkUSpaMwfe/RqzY/RlqLXoIfQgdR5BamNVymDxDtYb9uT1et32lDy0KIwW+cbUydWbMQ0ZGUWfWmH0Ys6b7iEEiUBnlYrW9mQJ5GV7TztAYfpgl+cc8s/9GNsYvm5eEEUOOkpxpYxpKOQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Doc = _t, Client = _t, Code = _t, Date = _t, Year = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Doc", Int64.Type}, {"Client", Int64.Type}, {"Code", type text}, {"Date", type text}, {"Year", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Consecutive", each List.Accumulate({0..[Index]},1,(state,current)=> if current = 0 then state else if #"Added Index"{current}[Year]=#"Added Index"{current-1}[Year]+1 then state+1 else 1))
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".