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])))
[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
Hi Jon,
You can try adding another filter criteria to CALCULATE: [Type] <> "Opening Customers"
Mynda
Hi Mynda
Thanks for your reply.
I added another filter criteria into the first formula listed above, however all it did was make Opening Customers blank without showing the opening balance for the start of each quarter.
Thanks
Jon
