Calculate at row level then Aggregate in a Measure

[Anonymous]

Hi everyone,

So my table looks like this

AccountNumber	F	C	Type	Period
A23234	$532	$85	I	12/31/2019
A56544	$3,323	$73	C	12/31/2019
A84736	$812	$22	C	12/31/2019
A99384	$729	$99	C	12/31/2019
A38394	$3,524	$2,363	I	12/31/2019
A23234	$539	$95	I	2/29/2020
A56544	$3,333	$72	C	2/29/2020
A84736	$812	$85	I	2/29/2020
A38394	$3,434	$2,323	I	2/29/2020

We get new account data every month end or so, we always want our comparisons compared to the last quarter end data. So in this instance 2/29 is our most recent period and we're comparing to 12/31. If the period is 5/31 then we'd compare it to 3/31, etc etc

This is the main formula I want to apply for each account, then add it all up for one final #

(Current Period [F] - Prior Period [F]) * (Prior Period [C] / Prior Period [F] )

Issues I'm having 

• If an account closed between the 2 periods, it won't be on 2/29 but I still want the formula applied to it, it'd just essentially be
   ( 0 - Prior Period [F]) * (Prior Period [C] / Prior Period [F] )
• If an account is type I and stays type I, I don't care about it. Only C's that stay C's and C's that become I's.

Any help would really be appreciated here, pulling my hair out trying to figure it out, thanks!

[v-lili6-msft]

HI  @Anonymous 

For your case, you could try this way as below:

Step1:

Add two dim tables and create two relationships as below:

Dim AccountNumber = VALUES('Table'[AccountNumber])

Dim Period = VALUES('Table'[Period])

Step2:

Create a measure as below:

Measure = 
VAR _CurrentPeriodF= CALCULATE(SUM('Table'[F]))
var _PriorPeriodF= CALCULATE(SUM('Table'[F]),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
VAR _PriorPeriodC= CALCULATE(SUM('Table'[C]),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
RETURN
(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)

If an account is type I and stays type I, I don't care about it. Only C's that stay C's and C's that become I's.

Do you mean you want to add a conditional that type is "C"?

for example:

Measure 2 = 
VAR _CurrentPeriodF= CALCULATE(SUM('Table'[F]),FILTER('Table','Table'[Type]="C"))
var _PriorPeriodF= CALCULATE(SUM('Table'[F]),FILTER('Table','Table'[Type]="C"),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
VAR _PriorPeriodC= CALCULATE(SUM('Table'[C]),FILTER('Table','Table'[Type]="C"),FILTER(ALL('Dim Period'),DATEDIFF('Dim Period'[Period],MAX('Dim Period'[Period]),MONTH)=2))
RETURN
(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)

Here is sample pbix file, please try it.

If not your case, please share your expected output based on the sample data.

Regards,

Lin

[Anonymous]

Thank you so so so much! Your sample file is very very close to what I need.

Here is what the calculations should be for each account

AccountNumber	F	C	Type	Period	Calculation	Notes
A23234	$532	$85	I	12/31/2019
A23234	$539	$95	I	2/29/2020	0	Account was type I in previous quarter so formula doesn't apply
A38394	$3,524	$2,363	I	12/31/2019
A38394	$3,434	$2,323	I	2/29/2020	0	Account was type I in previous quarter so formula doesn't apply
A56544	$3,323	$73	C	12/31/2019
A56544	$3,333	$72	C	2/29/2020	0.219681011	(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)
A84736	$812	$22	C	12/31/2019
A84736	$812	$85	I	2/29/2020	0.000000	(_CurrentPeriodF-_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)
A99384	$729	$99	C	12/31/2019	-99	(0 -_PriorPeriodF)*(_PriorPeriodC/_PriorPeriodF)
					-98.780319	Final Number I want

Differences to what is in the sample pbix

• Measure 2 which filters for type C in previous period doesn't seem to be working? No row level #s are popping up
• The total numbers are doing the calculation at the aggregate level, while I'd like the row level calculations to be added instead. Difference for Measure 1 is 2404.55 in sample file vs -98.78 above
• For account A84736 there seems to be an issue since it changed from C to I, i still want the calc applied to that account, it's creating 3 rows something is happening there
• For most of these reports I'm going to have a slicer at the top where I can look at one certain period, when I do that in the report it makes all the values 0? 
• It looks like you're DATEDIFF is comparing one date and then adding 2 months to it, I always want it compared to the previous quarter end period, i tried doing something like ENDOFQUARTER(PREVIOUSQUARTER(PERIOD)) but I keep getting errors. This table should help explain better?

Period I'm filtering on	Period to Compare Against
1/31/2020	12/31/2019
2/29/2020	12/31/2019
3/31/2020	12/31/2019
4/30/2020	3/31/2020
5/31/2020	3/31/2020
6/30/2020	3/31/2020

Thanks again, I can't even put into words how much this has helped already @v-lili6-msft