cannot return revenue of latest and previous year when a slicer is selected

[cheryl3030]

Below is an example.

I want to create two cards to display the revenue for latest year and previous year respectively.

revenue for latest year = calculate(sum(Sheet1[Revenue]), 'Sheet1'[Date].[Year] = MAX('Sheet1'[Date].[year]))

revenue for previous year = calculate(sum(Sheet1[Revenue]), 'Sheet1'[Date].[Year] = MAX('Sheet1'[Date].[year])-1)

These formulas work correctly when no slicer is selected.

However, when I select the slicer - 2021, the result appears blank.

The revenue of the latest year (2021) should be 700 and revenue of the previous year (2020) should be 300.

Thank you!

[amitchandak]

@cheryl3030 . Try below measures. But best is use date table having a year column and avoid date hierarchy

revenue for latest year =
var _max = Maxx(allselected('Sheet1') ,'Sheet1'[Date].[year])
return
calculate(sum(Sheet1[Revenue]), 'Sheet1'[Date].[Year] = _max)

revenue for previous year =
revenue for latest year =
var _max = Maxx(allselected('Sheet1') ,'Sheet1'[Date].[year])
return
calculate(sum(Sheet1[Revenue]), 'Sheet1'[Date].[Year] = _max-1)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[cheryl3030]

Thank you for your reply.

The formula of revenue for latest year works well, but the result of revenue for previous year still shows blank. Do you have any idea?