Measure to calc average price from prior year and prior cohort

[sglendenning]

Trying to create a measure to get the average price from the prior year in the previous cohort.

I have a data table that looks like the following as an example;

Product	Cohort	SaleDate	Price
A	1	Aug-20	$        200
A	1	Aug-20	$        200
A	2	Aug-20	$        250
A	2	Aug-20	$        250
A	2	Aug-21	$        400
A	3	Aug-21	$        600
A	3	Aug-21	$        600
A	3	Aug-20	$        400
A	3	Aug-20	$        400

Pivot of the data and I get;

In Power BI I'm having issues with a measure that would give me prior year and prior cohort...using the pivot as an example I need to subtract the $400 in from Cohort 2 in Aug of 2021 from the $200 of Cohort 1 in Aug of 2020 (as another example it would be the $600 in cohort 3 Aug 2021 minus $250 from cohort 2 in Aug 2020).

[v-xiaoyan-msft]

Hi @sglendenning ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC]

Hi @sglendenning 

Please create these measures:

1. Avg Price = AVERAGE ( Table1[Price] )

2. CurYr = CALCULATE ( [Avg Price] , FILTER ( Table1 , Table1[SaleDate].[Year] = MAX ( Table1[SaleDate].[Year] ) ) )

3. LstYr = CALCULATE ( [Avg Price] , FILTER ( Table1 , Table1[SaleDate].[Year] = ( MAX ( Table1[SaleDate].[Year] ) -1 ) ) )

[sglendenning]

Apologies as I don't think I explained very well.  This is really close but I need to get the price for the previous year and previous cohort.  In the LstYr column Cohort 1 would essentially have no value, Cohort 2 would be $200, and Cohort 3 would be $250.  

Product	LstYr	CurYr
A
1
2	$200	$400
3	$250	$600

[TheoC]

@sglendenning , My apologies.  Are you able to explain how you got $250 for LstYr A3? Or should this be $350 ($600 - $250)? Once confirmed, will work it out.

Thanks heaps and my bad on the misunderstanding!

[sglendenning]

@TheoC , Last year's cohort 2 becomes this year's cohort 3.  The cohorts are just identifying a customer and their continued renewal of a product year over year (I'm probably causing confusion by using the term cohort when it is really just the renewal progression of a customer.  For example someone who is a 3 this year was a 2 last year and a 1 the year before).  I'm trying to identify the price increase they are seeing over years.  

[TheoC · Accepted solution]

@sglendenning that is my apologies mate. I didn't read it correctly.

Okay, please perform the following:

1. Create a measure called "CurYr" like previous
   • CurYr =
     CALCULATE ( [Avg Price] ,
     FILTER (
     Table1 , Table1[Cohort] = MAX ( Table1[Cohort] ) &&
     Table1[SaleDate].[Year] = MAX ( Table1[SaleDate].[Year] ) )
     )
2. Create a calculated column called "Cohort 2" to give you the prior Cohort you're after.
   • Cohort (2) = Table1(Cohort) - 1
3. Create a second calculated column to return the Cohort 2 price you are after
   • Previous Cohort Price =

     IF ( Table1[Cohort (2)] = 0 , 0 ,

     CALCULATE (

     FIRSTNONBLANK ( Table1[Price] , 1 ) ,

     FILTER ( ALL ( Table1 ) , Table1[Cohort (2)] )))
4. Create a measure as follows:
   • CurYr v PriorCohort = [CurYr] - AVERAGE ( Table1[Previous Cohort Price] )

Hope this is what you were after! 🙂