Forum Discussion

Joseds's avatar
Joseds
New Member
4 years ago
Solved

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

Data Modeling
Reports
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

     

     

     

2 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.