Counting / Analyzing Results from a Measure

From a list of subscription records with ending subscription dates, I’ve created a measure to compute “Days To Renewal Due Date” for each subscription at any given date.

SubscriptionRecords

  1. Customer ID
  2. EndDateSubscription

Days To Renewal Due Date =
CALCULATE(
DATEDIFF(
MIN(Dates[Date]),
MAX(‘SubscriptionRecords’[EndDateSubscription]),
DAY))

I would now like to group the number of subscriptions by the “Days till Renewal Date” into groupings of “1-30days”, “31-60days”, “61-90days”, “>90 days”.

As the “Days till Renewal Date” is itself a Measure, Power BI somehow doesn’t allow me to create another measure to count the number of subscriptions for each specific “Days till Renewal Date” to answer the simple question – “How Many Subscriptions Are Due for Renewal in X Days?”

Appreciate the community’s and Sam’s help on this please…

Hello,
Try using that ‘Days To Renewal Due Date’ as your base measure to build off and make separate measures for those Days groupings.
*DTR = Days To Renewal
Does this measure work for providing only the 1-30 days?
DTR <30 = CALCULATE([Days To Renewal Due Date], [Days To Renewal Due Date]<31)

If so then you should also be able to use these:
DTR 31-60 = CALCULATE([Days To Renewal Due Date], [Days To Renewal Due Date]>30, [Days To Renewal Due Date]<61)
DTR 61-90 = CALCULATE([Days To Renewal Due Date], [Days To Renewal Due Date]>60, [Days To Renewal Due Date]<91)
DTR >90 = CALCULATE([Days To Renewal Due Date], [Days To Renewal Due Date]>90)

Andrew

Try using a SWITCH statement measure.

SWITCH (
          TRUE (),
          [Days to Renewal Due Date] >0 &&  [Days to Renewal Due Date] <=30, "1-30days",
          [Days to Renewal Due Date] >30 &&  [Days to Renewal Due Date] <=60, "31-60days",
         [Days to Renewal Due Date] >60 &&  [Days to Renewal Due Date] <=90, "61-90days",
         BLANK () )

Here is a great video explaining the concept.

Cheers!
image