Calculate Sales for Previous Year based on Year selected in the Filter panel

[vanessa]

Hi,

I have 2 tables that are connected via the YearID field:

Fact_Sales:

YearID

Product

Sales

Dim_Year:

YearID

Year

I have a Year filter in the Filter panel.

I want to display the Product wise sales for the selected year and the previous year on a bar chart.

Since the data granularity is at Year level, I cannot use PARALLELPERIOD() or SAMEPERIODLASTYEAR() to calculate the sales for the previous year. 

how can I calculate the sales for the previous year?

[vanessa]

Thank you @Jihwan_Kim , @amitchandak  This worked

There is a slight change in my requirement:

I have 2 tables that are connected via the Year_Quarter field:

Fact_Sales:

YearID

QuarterID

Year_Quarter

Product

Sales

Dim_Year:

YearID

Year

QuarterID

Year_Quarter

I have 2 filters in the Filter panel: Year, Quarter

I want to display the Product wise sales for the selected year, quarter combination and same quarter previous year on a bar chart.

how can I calculate the sales for the same quarter previous year?

[Jihwan_Kim]

Hi, @vanessa 

Please try the below measure.

Or, please share your sample pbix file's link here, then I can try to look into it, and come up with a more accurate measure.

Sales Total Previous Year same quarter =

VAR currentquarter = MAX('Year'[QuarterID])

RETURN

CALCULATE (

Sales[Sales Total],

FILTER ( ALL ( 'Year' ), 'Year'[Year] = MAX ( 'Year'[Year] ) - 1 && 'Year'[QuarterID] = currentquarter )

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[vanessa]

@Jihwan_Kim thank you very much, but the measure doesn't seem to work.

Also, mutilple years and quarters can be selected from the filter panel, so the logic with 'Max' won't work, right?

[Jihwan_Kim]

Hi, @vanessa 

Thank you very much for your feedback.

I think, in general, it should work.

Perhaps, I missed something from your table structure.

Please try the below, or please share your sample pbix file's link here, then I can try to come up with a more accurate solution.

Sales Total Previous Year same quarter =

VAR currentquarter = MAX('Year'[QuarterID])
VAR currentyear = MAX('Year'[YearID])

RETURN

CALCULATE (

Sales[Sales Total],

FILTER ( ALL ( 'Year' ), 'Year'[Year] = currentyear - 1 && 'Year'[QuarterID] = currentquarter )

)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Jihwan_Kim]

Hi, @vanessa 

Please check  the below picture and the sample pbix file's link down below.

Sales Total Previous Year =
CALCULATE (
Sales[Sales Total],
FILTER ( ALL ( 'Year' ), 'Year'[Year] = MAX ( 'Year'[Year] ) - 1 )
)

https://www.dropbox.com/s/l9q4i8k7r9cfpb5/vanessa.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[amitchandak]

@vanessa , Try like

This Year = CALCULATE(sum('Fact_Sales'[sales]),filter(ALL('Dim_Year'),'Dim_Year'[Year]=max('Dim_Year'[Year])))
Last Year = CALCULATE(sum('Fact_Sales'[sales]),filter(ALL('Dim_Year'),'Date'[Dim_Year]=max('Dim_Year'[Year])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])