Show Previous Year In Different Row.

[Ian_Tan]

Hi, 

I have a special requirement, we have one table 

• ID
• Date
• Completed(1 or 0)
• Type

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.

[v-lili6-msft · Accepted 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