Power Pivot - Customer numbers by quarter

[jon999]

Hi

I have a table that looks like this

Type	Jan	Feb	Mar	Apr
Opening customers	100	110	135	160
New	20	50	30	15
Churn	-10	-25	-5	-20
Closing customers	110	135	160	155

I am trying to work out the DAX formula to be able to select a month (via a slicer) that will give me the quarter view.

For example, select Feb and get the following:

Opening customers 100 (being Jan)
New 70 (being Jan + Feb)
Churn -35 (being Jan + Feb)
Closing customers 135 (being Feb closing customers)

When I select Apr

Opening customers 160 (being Apr opening customers)
New 15 (being Apr)
Churn -20 (being Apr)
Closing customers 155 (being Apr closing customers)

I have unpivoted the table and did a sum formula on the type (called Total Type) and then tried the following formula

Calculate([Total Type],Filter(All(Date),Date[YearQuarterNumber] = Max(Date[YearQuarterNumber]) && Date[Date] <= Max(date[Date])))

This works for new and churn but adds the opening and closing numbers.

Thanks