Continuous Years

Hi Anonymous,

The reason why it shows FY9 for 2009 instead of FY09 is that the Function VALUE()

The FORMAT() you used will return a text type 09, but when the text input VALUE(), it will be translated to a number 9.

Use this dax to create a column named Financial Year:

Financial Year =
VAR A =
    IF(
        [DATE].[MonthNo] > 8,
        FORMAT( DATE( YEAR( [DATE] ) + 1, 1, 1 ), "yy" ),
        FORMAT( [DATE], "yy" )
    )
RETURN
    CONCATENATE( "FY", A )

Going back to your case, you need to calculate the number of years it lasts.

Create a column which is useful for calculations:

Fyear =
IF( MONTH( [DATE] ) > 8, YEAR( [DATE] ) + 1, YEAR( [DATE] ) )

Try this measure to count the number of years of conitiunous donating:

Continuous Years =
VAR _IDGroupTable =
    FILTER( ALL( 'Table' ), [ID] = SELECTEDVALUE( 'Table'[ID] ) )
VAR _AConY =
    MAXX( _IDGroupTable, [Fyear] ) - MINX( _IDGroupTable, [Fyear] ) + 1
VAR _FConY =
    COUNTROWS(
        DISTINCT( SUMMARIZE( _IDGroupTable, 'Table'[ID], 'Table'[Fyear] ) )
    )
RETURN
    IF( _AConY = _FConY, _FConY, BLANK() )

Here is my pbix file ,you can reference.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.