Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a special requirement, we have one table
I want to show UI Like this
for Target is another standard table that relate to Year. Ratio will be DIVIDE(COUNT(ID), SUM(Completed)),
I cant force customer select two year, their requirement is only select one year.
now I have two issue:
1. How to automaticlly generate two year for No Of Completed and Ratio
2. How to only show one year to Target.
Appreciate your help.
Solved! Go to Solution.
hi, @Ian_Tan
Based on my test, you could try this way:
1. You could use SAMEPERIODLASTYEAR Function to achieve it.
Step1:
Create a date table, then add Year/ Month/ YearMonth column for it.
Step2:
Create two measure
current year = CALCULATE(SUM('Table'[Completed])) last year = CALCULATE([current year],SAMEPERIODLASTYEAR('Date'[Date]))
Step3:
Drag Type and Year into Rows Value Month into Columns Value and two measure into Values Value
Step4:
Turn on Format->Values->Show on rows
Turn off Format->Subtotals->Row subtotals and Column subtotals
2.
Step1:
Create a YearMonth for target table
Step2:
Create a relationship between target table and date table by yearmonth column
set Cross filter direction is "Both"
Step3:
Create a target measure
Target = CALCULATE(SUM(Target[Tar]))
Step4:
Drag this measure into Value
and here is a sample pbix, please try it.
Best Regards,
Lin
hi, @Ian_Tan
Based on my test, you could try this way:
1. You could use SAMEPERIODLASTYEAR Function to achieve it.
Step1:
Create a date table, then add Year/ Month/ YearMonth column for it.
Step2:
Create two measure
current year = CALCULATE(SUM('Table'[Completed])) last year = CALCULATE([current year],SAMEPERIODLASTYEAR('Date'[Date]))
Step3:
Drag Type and Year into Rows Value Month into Columns Value and two measure into Values Value
Step4:
Turn on Format->Values->Show on rows
Turn off Format->Subtotals->Row subtotals and Column subtotals
2.
Step1:
Create a YearMonth for target table
Step2:
Create a relationship between target table and date table by yearmonth column
set Cross filter direction is "Both"
Step3:
Create a target measure
Target = CALCULATE(SUM(Target[Tar]))
Step4:
Drag this measure into Value
and here is a sample pbix, please try it.
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
105 | |
88 | |
75 | |
67 |
User | Count |
---|---|
123 | |
112 | |
96 | |
82 | |
72 |