Forum Discussion

BI_Power_Guy's avatar
BI_Power_Guy
Frequent Visitor
4 years ago

Dynamic Current Year vs Last Year sales , Using between date slicer( date slider) .

Hi Folks , I want to build current year to last year sales , dynamically using date slider .(Not using year filter , but only by date slider) I am not able to limit date slicer to only incl...
Tips and Tricks
rbriga's avatar
rbriga
Icon for Impactful Individual rankImpactful Individual
4 years ago

Hi BI_Power_Guy , thanks for the mention.

Here's how I'd solve this.

Suppose the user used the date slicer like your example, 11-09-2020 to 14-01-2022.

We can create measures to return the current year (CY) value as 01-01-2022 to 14-01-2022,

And last year (LY) as 01-01-2021 to 14-01-2021.

 

Here's what worked for my tests:

Sales CY = 
CALCULATE(
    [Sales],
    KEEPFILTERS('Sales_date_dim_copy(ref_for_sbdt)'[Year] = MAX('Sales_date_dim_copy(ref_for_sbdt)'[Year]))
    )
Sales LY = 
CALCULATE(
    [Sales],
SAMEPERIODLASTYEAR('Sales_date_dim_copy(ref_for_sbdt)'[Date]),
    KEEPFILTERS('Sales_date_dim_copy(ref_for_sbdt)'[Year] = MAX('Sales_date_dim_copy(ref_for_sbdt)'[Year])-1)
    )

 

Give it a try!