SSAS Tabular - how to aggregate differently at month grain?

Question

In my cube, I have several measures at the day grain that I'd like to sum at the day grain but average (or take latest) at the month grain or year grain.

Example: We have a Fact table with Date and number of active subscribers in that day (aka PMC). This is snapshotted per day.

dt	SubscriberCnt
1/1/22	50
1/2/22	55

This works great at the day level. At the month level, we don't want to sum these two values (count = 105) because it doesn't make sense and not accurate.

when someone is looking at month grain, it should look like this - take the latest for the month. (we may change this to do an average instead, management is still deciding)

option 1 - Take latest

Month-Dt	Subscribers
Jan-2022	55
Feb-2022	-

option 2 - Take aveage

Month-Dt	Subscribers
Jan-2022	52
Feb-2022	-

I've not been able to find the right search terms for this but this seems like a common problem.

Answer 1

I added some sample data at the end of a month for testing:

dt	SubscriberCnt
12/30/21	46
12/31/21	48

This formula uses LASTNONBLANKVALUE, which sorts by the first column and provides the latest value that is not blank:

Monthly Subscriber Count = LASTNONBLANKVALUE( 'Table'[dt], SUM('Table'[SubscriberCnt]) )

If you do an AVERAGE, a simple AVERAGE formula will work. If you want an average just for the current month, then try this:

Current Subscriber Count = 
VAR _EOM = CLOSINGBALANCEMONTH( SUM('Table'[SubscriberCnt]), DateDim[Date] )
RETURN IF(_EOM <> 0, _EOM, AVERAGE('Table'[SubscriberCnt]) )

But the total row will be misleading, so I would add this so the total row is the latest number:

Current Subscriber Count = 
VAR _EOM = CLOSINGBALANCEMONTH( SUM('Table'[SubscriberCnt]), DateDim[Date] )  //Get the number on the last day of the month
VAR _TOT = NOT HASONEVALUE(DateDim[MonthNo])  // Check if this is a total row (more than one month value)
RETURN IF(_TOT, [Monthly Subscriber Count],   // For total rows, use the latest nonblank value
          IF(_EOM <> 0, _EOM, AVERAGE('Table'[SubscriberCnt]) )  // For month rows, use final day if available, else use the average
        )