cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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;

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

1 ACCEPTED SOLUTION
Super User

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

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

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!

6 REPLIES 6
Community Support

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.

Super User

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

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!

Helper I

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

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

Helper I

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

Super User

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

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

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors