Customer numbers by quarter|Power Pivot|Excel Forum|My Online Training Hub

Hi

I have a table that looks like this

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])))

[YearQuarterNumber] = ( 'Date'[Financial Year] - MIN ( 'Date'[Financial Year] ) ) * 4  + [QuarterNumber]

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

Thanks

Jon