Quarter view with static numbers and sum numbers

[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 number)
New 70 (being Jan + Feb numbers)
Churn -35 (being Jan + Feb numbers)
Closing customers 135 (being Feb closing customers or adds opening + new + churn)

When Apr is selected, the result should be:

Opening customers 160 (being Apr opening customers)
New 15 (being Apr numbers)
Churn -20 (being Apr numbes)
Closing customers 155 (being Apr closing customers or adds opening + new + churn)

Thanks

Jon

[Anonymous]

Hi @jon999 ,

You can refer the following links to get it:

Create a chart to show Jan - December and selected month

Cumulative Revenue by Plan

If the above one can't help you get the desired result, please provide some sample data with Text format(exclude sensitive data) and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[jon999]

I have created a sample excel file to show what I need. From the file, you can see that the new and churn numbers are working based on the measure, but it is the open and closing numbers that I can not work out how to do that in the measure.

https://www.dropbox.com/s/hyzmuz0rdxzh201/Customers.xlsx?dl=0

[jon999]

Thanks @amitchandak for replying.

I was hoping to be able to have it in one measure where you can click on a filter and the table would update. For example select Feb (from a filter), and opening balance would be Jan, sum Jan and Feb for New and churn and Closing balance would be Feb. 

I have unpivoted the data.

Thanks

Jon

[amitchandak]

@jon999 , Unpivot the table to have a month on a row or better transpose. Create date with help from month

Then use measure like

For New use QTD

QTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

For Opening use

QTD First Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,mod(Month(_max1),3)))+1

var _max = eomonth(_min,0)
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))