How to compare the values based on the user's selected year Vs previous year.

[Chari]

Hello All....
I have a date table and sales tables; in the sales table, I have 3 columns: WL, date, and amount.

Requirement:
if we select any year from year dropdown, it should compare with previous year

Ex1: If we choose 2024, the comparison should be 2024 vs. 2023, and it should show by quarterly.
Ex2: If we select 2020, the comparison should be 2020 against 2019, and it should show by quarterly.

The table should show like below.

----------------------------------------------------------------------
I created below measures

Previous = var _s = SELECTEDVALUE (Date[tDate]. [Year])
RETURN
CALCULATE(SUM(Sales[Amount]),FILTER(Sales,YEAR([Date])=_s))

, 

Previous = var _s = SELECTEDVALUE(Date[tDate]. [Year]) -1
RETURN
CALCULATE(SUM(Sales[Amount]),FILTER(Sales,YEAR([Date])=_s))

O/P with the above measures.
For select year (2023), the previous column is coming as blank, and the previous year (2022) current column is coming as blank.

For quarters, we have to go with the drill throw option. Without drillthrough, is there any other option?

I tried with field parameters also, but o/p is the same.

Parameter = {
("Current", NAMEOF('Measure'[Current]), 0),
("Previous", NAMEOF('Measure'[Previous]), 1)
}

[Anonymous · Accepted solution]

Hi @Chari 

Try changing your Previous measure using the time-intelligence function SAMEPERIODLASTYEAR() like this:

Previous = CALCULATE(
    SUM(SalesTable[Amount]),
    SAMEPERIODLASTYEAR(DateTable[Date])
)

Current measure:

Current = CALCULATE(
    SUM(SalesTable[Amount]),
    YEAR(SalesTable[Date]) = SELECTEDVALUE(DateTable[Year])
)

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Chari 

Sorry to bother you.

I would like ask if my previous reply has helped you resolved the problem? If so, please consider accepting the previous reply as a solution. This will make it easier for other users experiencing the same problem to find a solution.

If you have any other questions, please feel free to contact me.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.