Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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).
Solved! Go to Solution.
@sglendenning that is my apologies mate. I didn't read it correctly.
Okay, please perform the following:
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
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.
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 ) ) )
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.
Product | LstYr | CurYr |
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 that is my apologies mate. I didn't read it correctly.
Okay, please perform the following:
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
86 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
110 | |
97 | |
71 | |
67 |