Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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% |
Solved! Go to Solution.
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
_min
The 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!
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
_min
The 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!
@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])