Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sglendenning
Helper I
Helper I

Measure to calc average price from prior year and prior cohort

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;

ProductCohortSaleDatePrice
A1Aug-20 $        200
A1Aug-20 $        200
A2Aug-20 $        250
A2Aug-20 $        250
A2Aug-21 $        400
A3Aug-21 $        600
A3Aug-21 $        600
A3Aug-20 $        400
A3Aug-20 $        400

 

Pivot of the data and I get;

sglendenning_0-1632343553167.png

 

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).

1 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] )

TheoC_0-1632879851300.png

 

Hope this is what you were after! 🙂 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

6 REPLIES 6
v-xiaoyan-msft
Community Support
Community Support

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
Super User
Super User

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 ) ) )

TheoC_0-1632373100465.png

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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.  

 

ProductLstYrCurYr
A  
1  
2$200$400
3$250$600

@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!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@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.  

 

sglendenning_1-1632765040559.png

 

 

@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] )

TheoC_0-1632879851300.png

 

Hope this is what you were after! 🙂 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.