Time intelligence Time Slicer

Hi, I work at a major NGO and am still relatively new to Power BI and DAX.

I have an analyst who is requesting a Power BI report that can tell a monthly donor (ie. someone who gives us a gift on a regular monthly basis. Ie. IsSustainerPayment=1) within a specific Fiscal Year and Fiscal Period/Month (multiselect on the Fiscal Period, single select on Fiscal Year). Based on the minimum gift date of the first sustainer payment in the filtered dataset, the analyst wants a count of one-time cash gifts (i.e. not regular sustainer gifts, but additional donations on top of their recurring monthly gifts…AKA IsSustainerPayment =1). I like to figure things out for myself but I am really stuck.

The analyst wants to be able to look at the changes to donor behaviors at specific intervals after the min sustainer gift date (i.e. 3 months after, 6 months, etc) and grouped by the number of cash gifts made (No Cash Gifts, 1 Cash Gift, 2-5 Cash Gifts etc.).

It is the combination of the two groupings that is causing the greatest confusion, i.e. how to apply an ALL after a specific date beyond a fiscal year and month where the fiscal year can exclude one-time cash gifts made in FY20 (and 12 months after the first sustainer gift)

Based on the above filter I would expect to be looking at the cohort of all gifts of donors (or counts of donors themselves) where the sustainer gift date is somewhere within April 2019 and of those donors, how many made cash gifts, how many gifts were made and the value of those gifts (both average and total sum). The table should reflect gifts BEYOND Fiscal Year = 2019 and Fiscal Month = April (i.e 18, 24 etc. months after the min date)

Here's a sample of data from the source Table (‘Gift Query’)

I will note that I do NOT have a calendar dimension and need to pull all the necessary dates from the above table as I am working in an existing report that does not have one.

Here’s the DAX I have so far for Gift Counts, but I know is wrong:

Gift Counts =

 var selectDate = Calculate(Min('Gift Query'[GiftDate]),ALLSELECTED('Gift Query'[GiftDate]))

var selectedFunction = SELECTEDVALUE('Gift Month Table'[Sort Order])

var isSustainerTab =

     ADDCOLUMNS(

          DISTINCT('Gift Query'[AccountSFID]),

          "Min_Date",

           CALCULATE(MIN('Gift Query'[GiftDate]),'Gift Query'[IsSustainerPayment]=1),

          "Max_Date",

           CALCULATE(Max('Gift Query'[GiftDate]),'Gift Query'[IsSustainerPayment]=0),

          "Gift_Count",

          var t =

          FILTER('Gift Query','Gift Query'[AccountSFID]=EARLIER('Gift Query'[AccountSFID]))

          var min_date =

          CALCULATE(MIN('Gift Query'[GiftDate]),'Gift Query'[IsSustainerPayment]=1)

          return

          COUNTROWS(FILTER(t,'Gift Query'[IsSustainerPayment]=0 && 'Gift Query'[GiftDate] > min_date)) + 0

     )

 VAR GiftCount=

  SWITCH(selectedFunction,

"1",CALCULATE(SUMX(FILTER(isSustainerTab,NOT ISBLANK([Min_Date]) && [Max_Date]>selectDate && [Max_Date] <=selectDate+90),[Gift_Count])),

"2",CALCULATE(SUMX(FILTER(isSustainerTab,NOT ISBLANK([Min_Date]) && [Max_Date]>selectDate && [Max_Date] <=selectDate+180),[Gift_Count])),

"3",CALCULATE(SUMX(FILTER(isSustainerTab,NOT ISBLANK([Min_Date]) && [Max_Date]>selectDate && [Max_Date] <=selectDate+365),[Gift_Count])),

"4",CALCULATE(SUMX(FILTER(isSustainerTab,NOT ISBLANK([Min_Date]) && [Max_Date]>selectDate && [Max_Date] <=selectDate+540),[Gift_Count])),

"5",CALCULATE(SUMX(FILTER(isSustainerTab,NOT ISBLANK([Min_Date]) && [Max_Date]>selectDate && [Max_Date] <=selectDate+730),[Gift_Count])))

The switches represent the 3,6,12,18,24 months chiclet filter on the screen.

The ranges are leveraging a helper table I created (‘Gift Group’):

Thanks in advance for your help!