tricky measurement [this quarter numerator over last quarter denominator]

[shower999]

All expert,

I have an easy metrics but I couldn't resolve it in hour

I have a table with these 4 fields

[Accts_Status] = Text, "Attrited_Accts" and etc
[Accts_with_Asset] = 1 or 0
[EOQ_Date].[Date] = Date

basically I need to use a measurement to divide 'count of "attrited_Accts" over last quarter' 'sum of [Accts_with_Asset]

for an example, I have below aggregated data

sum of [Accts_with_Asset] by Data_Qtr

FY19Q1  116,019

FY19Q2  121,609

FY19Q3  128,840

Count of [Accts_Status] filter in "Attrited_Accts" by Data_Qtr

FY19Q1  2,842

FY19Q2  5260

FY19Q3  4,774

My expected results is

FY19Q1  null

FY19Q2  4.5% (5260 / 116019)

FY19Q3  3.9% (4774 / 121609)

for some reason, the measurement only show me below which is not my expectation

FY19Q1  2.45% 

FY19Q2  4.33% (5260 / 121609)

FY19Q3

here is the code I use

Account_Attrition_rate = 
IF(
	ISFILTERED('PowerBI_RenewalReport'[EOQ_Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __Attrited_Accts =
    CALCULATE(
    	COUNTA('PowerBI_RenewalReport'[Accts_Status]),
    	'PowerBI_RenewalReport'[Accts_Status] IN { "Attrited_Accts" }
    )
VAR __PREV_QUARTER =
		CALCULATE(
			SUM('PowerBI_RenewalReport'[Accts_with_asset]),
			DATEADD('PowerBI_RenewalReport'[EOQ_Date].[Date], -1, QUARTER)
		)
RETURN
    FORMAT(DIVIDE(
        __Attrited_Accts,
        __PREV_QUARTER)
        ,"0.00%")
)

[shower999 · Accepted solution]

hi

i want to thanks @v-piga-msft for the learning.

I manage to use my own measure to resolve this easily.

here is the measure I used without indexing. I don't know why it didn't work at first place. but it's working no

Measure = 
IF(
	ISFILTERED('Report'[EOQ_Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __PREV_QUARTER =
		CALCULATE(
			SUM('Report'[Accts_with_Asset]),
			DATEADD('Report'[Date].[Date], -1, QUARTER)
		)
	RETURN
		DIVIDE(
			SUM('Report'[Accts_with_Asset])
				- __PREV_QUARTER,
			__PREV_QUARTER
		)
)

[v-piga-msft]

Hi @shower999,

If the sum of [Accts_with_Asset] and Count of [Accts_Status] are columns, you could follow the steps below.

1. Create the Index column in Query Editor.

2. Create the calculated column with the formula below.

Column =
CALCULATE (
   MAX ( Table1[Accts_with_Asset] ),
   FILTER ( 'Table1', 'Table1'[Index] < EARLIER ( 'Table1'[Index] ) )
)

3. Create the measure below.

Measure = DIVIDE(MAX('Table1'[Accts_Status]),MAX('Table1'[Column]))

Here is the output.

If you still need help, could you share your data sample as table format and your desired output so that I could understand your requirement better and get the solution.

Best Regards,

Cherry

[shower999]

hi, @v-piga-msft 

I'm still stuck at step 2.

now I got this error says 'there is not enough memory to complete this operation'

is there any other work around that need less memory?

[shower999]

hi, @v-piga-msft

I sorted it with acsending to ensure the index works

but I am not able to get through step 2.

it says EARLIER/EARLIEST refers to an earlier row context which does not exist

I think that maybe because the [Accts_with_asset] data are all 0 because it supposed to be 0 instead of 1 for this [Accts_Status]

so it won't work with the max.

here are some sample of the data

Data_Qtr	account_id	Accts_Status	Accts_with_Asset
FY18Q1		496		Attrited_Accts			0
FY18Q1		1094		Attrited_Accts			0
FY18Q1		1343		Attrited_Accts			0
FY18Q1		1447		Attrited_Accts			0
FY18Q1		1575		Attrited_Accts			0