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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
sglendenning
Helper II
Helper II

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.