cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joseds
New Member

Current Year vs Last Year by Item

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

YearProductAmount
2021Current Product10000
2021Discontinued Product20000
2022Current Product30000
2022New Product40000

 

The Visual i want :

ProductSelected YearPrior Year
Current Product3000010000
New Product400000
Discontinued Product020000
TOTAL7000030000

 

The Visual i get:

ProductSelected YearPrior Year
Current Product3000010000
New Product400000
TOTAL7000030000

 

Selected year measure = sum(sales[Amount])

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

 

I am obviously missing something. 

 

Regards

Jose

1 ACCEPTED SOLUTION
Ailsa-msft
Community Support
Community Support

Hi @Joseds 

You can achieve the result you want with Pivot .

Original data :

Ailsamsft_0-1654244668431.png

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

Ailsamsft_1-1654244725354.png

You will get a result like this .

Ailsamsft_2-1654244833483.png

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

Ailsamsft_3-1654244925083.pngAilsamsft_4-1654244942884.png

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 .

Ailsamsft_5-1654245124201.png

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.

 

 

 

 

 

View solution in original post

2 REPLIES 2
Ailsa-msft
Community Support
Community Support

Hi @Joseds 

You can achieve the result you want with Pivot .

Original data :

Ailsamsft_0-1654244668431.png

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

Ailsamsft_1-1654244725354.png

You will get a result like this .

Ailsamsft_2-1654244833483.png

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

Ailsamsft_3-1654244925083.pngAilsamsft_4-1654244942884.png

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 .

Ailsamsft_5-1654245124201.png

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
Community Support
Community Support

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors