Forum Discussion
Continuous Years
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
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.