Forum Discussion
Create a column with value from calculated column for prior quarter
I'm relatively new to Power BI and I'm struggling to figure out the appropriate DAX formula to achieve the following:
I want to create a column that retrieves the prior quarter's value for a measure called "% of Target" so that I have a column for the current quarter's % of target and a column for the prior quarter's % of target. My table has 100's of items that show values for 12 quarters so I want to use the values in the columns at the row-level in my reports.
The table below shows the last column, which is the one I wanted to generate.
| Title | Target | Progress | Unit | Period End | % of Target | Prior Qtr - % of Target |
| Associate engagement score | 81.0 | 81.0 | unit | 3/31/2021 | 100% | |
| Associate engagement score | 81 | 82 | unit | 6/30/2021 | 101% | 100% |
| Cloud Training Certifications | 100.0 | 98.0 | % | 3/31/2021 | 98% | 101% |
| Cloud Training Certifications | 100 | 94 | % | 6/30/2021 | 94% | 98% |
| Expense | 56231.0 | 42000.0 | $ | 3/31/2021 | 75% | 94% |
| Expense | $ 96,532 | $ 94,545 | $ | 6/30/2021 | 98% | 75% |
| Interlock Revenue Attainment | 100.0 | 100.0 | % | 3/31/2021 | 100% | 98% |
| Interlock Revenue Attainment | 100 | 90 | % | 6/30/2021 | 90% | 100% |
| Net Promoter Score (NPS) | 59.0 | 57.0 | unit | 3/31/2021 | 97% | 90% |
| Net Promoter Score (NPS) | 60 | 61 | unit | 6/30/2021 | 102% | 97% |
| New Logos | 3.0 | 1.0 | unit | 3/31/2021 | 33% | 102% |
| New Logos | 5 | 4 | unit | 6/30/2021 | 80% | 33% |
| On-time Solution Implementation | 100.0 | 100.0 | % | 3/31/2021 | 100% | 80% |
| On-time Solution Implementation | 100 | 95 | % | 6/30/2021 | 95% | 100% |
| Product Quality – Avg # Issues | 8.8 | 6.0 | unit | 3/31/2021 | 68% | 95% |
| Product Quality – Avg # Issues | 8.4 | 8.4 | unit | 6/30/2021 | 100% | 68% |
| Product Quality – R&D Completion | 82.0 | 82.0 | % | 3/31/2021 | 100% | 100% |
| Product Quality – R&D Completion | 84 | 82 | % | 6/30/2021 | 98% | 100% |
| Product Quality – UDL Clean Run | 96.0 | 96.0 | % | 3/31/2021 | 100% | 98% |
| Product Quality – UDL Clean Run | 96 | 96 | % | 6/30/2021 | 100% | 100% |
| Revenue | 92112.0 | 920444.0 | $ | 3/31/2021 | 999% | 100% |
| Revenue | $ 90,223 | $ | 6/30/2021 | 0% | 999% | |
| Solution Roadmap On-time completion | 100.0 | 100.0 | % | 3/31/2021 | 100% | 0% |
| Solution Roadmap On-time completion | 100 | 88 | % | 6/30/2021 | 88% | 100% |
Hi Anonymous
You can create a Calculated column as the following.
Prior Qtr - % of Target = VAR cloest_title = MAXX ( FILTER ( 'Table', 'Table'[Title] < EARLIER ( 'Table'[Title] ) ), 'Table'[Title] ) VAR _min = MAXX ( FILTER ( 'Table', 'Table'[Title] = EARLIER ( 'Table'[Title] ) && 'Table'[Period End] < EARLIER ( 'Table'[Period End] ) || 'Table'[Title] = cloest_title && 'Table'[Period End] > EARLIER ( 'Table'[Period End] ) ), [% of Target] ) RETURN _minThe result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!
2 Replies
- amitchandak
Super User
Anonymous , Try a new column like
New column =
var _min = maxx(filter(Table, [Period end] <earlier([period end])),[period end])
return
maxx(filter(Table, [Period end] = _min),[% of Target]) 
v-cazheng-msftCommunity Support
Hi Anonymous
You can create a Calculated column as the following.
Prior Qtr - % of Target = VAR cloest_title = MAXX ( FILTER ( 'Table', 'Table'[Title] < EARLIER ( 'Table'[Title] ) ), 'Table'[Title] ) VAR _min = MAXX ( FILTER ( 'Table', 'Table'[Title] = EARLIER ( 'Table'[Title] ) && 'Table'[Period End] < EARLIER ( 'Table'[Period End] ) || 'Table'[Title] = cloest_title && 'Table'[Period End] > EARLIER ( 'Table'[Period End] ) ), [% of Target] ) RETURN _minThe result looks like this:
For more details, you can refer the attached pbix file.
Best Regards
Caiyun Zheng
If this post helps, please consider make it as the solution by Accept it as Solution. Really appreciate!
