Forum Discussion
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.
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.
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.
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?
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() )
