Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Continuous Years

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?
Need Help

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Still not working, as the same error before.;

      IDDateFinancial Year
      1111111January 2009FY9
      12434345Aug 2010FY11
      3432423412 Aug 2009FY10
      1111111January 2010FY10
      351241February 1978FY78
      6834347February 1978FY78
      1111111January 2009FY9
      12434545August 2009FY9

       

      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. 

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        PaulDBrown
        Community Champion
        4 years ago

        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?