Continuous Years

[Anonymous]

Hello, I have created the below measure to calcuate continuous years of giving. 

VAR __Previous = MAXX(FILTER('Gifts (2)','Gifts (2)'[constituent_id] = EARLIER('Gifts (2)'[constituent_id]) && [Financial Year] < EARLIER('Gifts (2)'[Financial Year])),'Gifts (2)'[Financial Year])

RETURN [Financial Year] - __Previous

However, the measure is saying its unable to convert value "" of type Text to Type Number based on this measure.

VAR fy =

IF (

MONTH ( 'Gifts (2)'[date] ) >8,

VALUE ( FORMAT ( 'Gifts (2)'[date], "YY" ) ) + 1, VALUE ( FORMAT ( 'Gifts (2)'[date], "YY" ) )

)

RETURN

CONCATENATE ("FY", CONCATENATE (fy,""))

Both calculated columns are set to text, so not sure why it is doing this. Only thing I can think of is that on the financial year column, it is meant to show as FY09 for year 2009. However, it is showing as only FY9, but other dates such as 1978 show as FY78, therefore its unable to convert it. How do I get that to be taking into consideration in the measure to rule it out?

[Khushidonda]

May i know how can i convert year column into

0-1 

1-2

3-4

5+ year

[v-chenwuz-msft]

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.

[Anonymous]

This appears to only look at number of years donating and not continuous. I forgot to mention it is only classed as continuous years by donating every year until the current year. 

[PaulDBrown]

Can you clarify the criteria? You say  "it is only classed as continuous years by donating every year until the current year"

Since you have dates going back to 1970, is it only continuous if there is a record per constituent id every year since 1970 until 2021?

[Anonymous]

Apologies, yes the criteria for continous years can only be for every year up until the year financial year. For instance, someone donating every financial year up until the current year would be 31 years of continuous donating. However, a measure is also needed for years of giving too, so if someone else had donated since 1990 but only donated in 10 seperate years, this would mean their lifetime of giving is 10 years. From the measure given below it does not work all the time, for instance those that have donated for 27 years are showing as blank when using the measure. Can you help with the continous years and lifetime giving measures? I included my report I did not use the fyear measure within it as I had a financial year that was working as appropriate.

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

[PaulDBrown]

I may be misunderstanding what you are looking for but...

The total number of Fiscal years from 1970 to 2021 is 53 (2 FY years in 1970 & 2 FY years in 2021.

To calculate whether an ID is "Continuous", the count of the IDs FY must equal 53 (none do)

But anyway, here is how:

Create a numerical column for FY in your table using:

 To calculate the total number of FY:

Total FY Years =
VAR MinY =
    CALCULATE ( MIN ( 'Gifts (2)'[FY WN] ), ALL ( 'Gifts (2)' ) )
VAR MaxY =
    CALCULATE ( MAX ( 'Gifts (2)'[FY WN] ), ALL ( 'Gifts (2)' ) )
VAR FY_table =
    GENERATESERIES ( MinY, MaxY, 1 )
RETURN
    COUNTROWS ( FY_table )

To calculate if an ID is "Continuous":

Continuous Every year = 
IF(DISTINCTCOUNT('Gifts (2)'[FY WN]) = [Total FY Years], "Continuous")

For Lifetime Giving (non continous)

Lifetime Giving = DISTINCTCOUNT('Gifts (2)'[FY WN])

If you want to just use a single measure to combine both results

Combined =
IF (
    DISTINCTCOUNT ( 'Gifts (2)'[FY WN] ) = [Total FY Years],
    "Continuous",
    FORMAT ( [Life Giving], "##0" )
)

To include the first FY for each ID

Since =
"FY"
    & RIGHT (
        CALCULATE (
            MIN ( 'Gifts (2)'[FY WN] ),
            ALLEXCEPT ( 'Gifts (2)', 'Gifts (2)'[constituent_id] ),
            'Gifts (2)'[grouped]
        ),
        2
    )

To calaculate the number of FY without a donation

FY without donation =
VAR MinY =
    CALCULATE (
        MIN ( 'Gifts (2)'[FY WN] ),
        ALLEXCEPT ( 'Gifts (2)', 'Gifts (2)'[constituent_id] )
    )
VAR MaxY =
    CALCULATE (
        MAX ( 'Gifts (2)'[FY WN] ),
        ALLEXCEPT ( 'Gifts (2)', 'Gifts (2)'[constituent_id] )
    )
VAR FY_table =
    GENERATESERIES ( MinY, MaxY, 1 )
RETURN
    COUNTROWS ( FY_table ) - [Lifetime Giving]

I've attached the sample PBIX file 

[Anonymous]

Hi, so continuous years the ID must have donated for more than one FY including the current FY. So an ID that has donated in FY17,18,19,20,21 would calculate to 5 years of continuous donating. However, if the same ID was to have also donated in FY11,12,13 but did not donate in any of the following years FY14,15,16, then donated from FY 17 to 21 there continous donating would be 5 years but their lifetime giving would equal to 8 years. I need two measures one to calculate lifetime giving and one to calculate the continous years of donating. I hope this makes sense, I will implement your measures and get back to you. Thank you for all your help, so far. 

[Anonymous]

This is sort of working, there is an individual that is showing as donating over 27 years, but its showing as blank on the power bi report. 

Power BI Report 

[amitchandak]

@Anonymous , try this change

AR __Previous = MAXX(FILTER(allselected('Gifts (2)'),'Gifts (2)'[constituent_id] = max('Gifts (2)'[constituent_id]) && [Financial Year] < max('Gifts (2)'[Financial Year])),'Gifts (2)'[Financial Year])
RETURN [Financial Year] - __Previous

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Still not working, as the same error before.;

ID	Date	Financial Year
1111111	January 2009	FY9
12434345	Aug 2010	FY11
34324234	12 Aug 2009	FY10
1111111	January 2010	FY10
351241	February 1978	FY78
6834347	February 1978	FY78
1111111	January 2009	FY9
12434545	August 2009	FY9

I'm wanting the FY9 to be displayed as FY09 and I am wanting to know the continuous years of gifting based on ID across Financial Years, they have to be continuous give across financial years to be classed as continuous donors. For instance, an ID that has gifted for FY 78, 79 and 80 would be 3 years of conitiunous donating.