Current Year vs Last Year by Item

[Joseds]

Hi

I am Struggling to write a measure to compare Last Year vs This Year by Item.

Firstly the years is not dates but text 2021 and 2022 and i cannot use dates because i would need to break it down further into financial months which is fiscal periods not dates, eg 1,2,3 .....12.

By selecting the year from a slicer to compare the selected year to prior year it omits the product that was discontinued and had sales in prior year but not in current year

The Table is

Year	Product	Amount
2021	Current Product	10000
2021	Discontinued Product	20000
2022	Current Product	30000
2022	New Product	40000

The Visual i want :

Product	Selected Year	Prior Year
Current Product	30000	10000
New Product	40000	0
Discontinued Product	0	20000
TOTAL	70000	30000

The Visual i get:

Product	Selected Year	Prior Year
Current Product	30000	10000
New Product	40000	0
TOTAL	70000	30000

Selected year measure = sum(sales[Amount])

Prior year measure = = CALCULATE(sum(sales[Amount]),sales[Year]="2021")

I am obviously missing something. 

Regards

Jose

[Ailsa-msft · Accepted solution]

Hi @Joseds 

You can achieve the result you want with Pivot .

Original data :

Choose Year and click Pivot , set Aggregate Value Function to Don't Aggregate . 

You will get a result like this .

If you don't want null , you can replace it with 0 in Power Query Editor .

Then back to Desktop view and add these columns in table visual , you will get a final result as shown below . You can rename column in Fields .

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-cazheng-msft]

Hi @Joseds,

For it's something related to write dax formula, we transfer it to DAX Commands and Tips forum to get help more quickly. 

Best Regards,

Community Support Team _ Caiyun