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

[BI_Power_Guy]

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 include 1 year of data at a time .So if this is not implemented it will be getting more then 1 year in selection and thus will result in wrong output.(as which year is the current year , it will not able to judge)

I have read some articles ,and not found if it is really possible to retrict date slider(and not relative date filtering).

Note: I have created a measure which is finding datediff between first and last date selected , and tried to put the measure on slicer visual filter to limit it , but that is not working.

CUSTOM_date_diff =

var min_date_sbdt1= FIRSTDATE('Sales_date_dim_copy(ref_for_sbdt)'[Date])

var max_date_sbdt1= LASTDATE('Sales_date_dim_copy(ref_for_sbdt)'[Date])

return

if(DATEDIFF(min_date_sbdt1,max_date_sbdt1,YEAR)<=1,1,2)

Any help on this will be really appreicated ,Thank You.

@amitchandak @rbriga 

[BI_Power_Guy]

Metric will show correctly for CY and LY sales values , but slicer value will going to confuse end user , if it shows more then 1 year value.

[BI_Power_Guy]

Hi @rbriga 
Thanks for replying , SALES CY , SALES LY i am able to find with samperiodlast year and your DAX also , however i wanted to know is there a way to limit date slider .
i found a article that provide same functionality , but by using 2 slicers(acting as FROM & TO date), but my requirement is to keep a single date slider slicer.

FYI , https://forum.enterprisedna.co/t/pbi-limit-date-period-selection/21951/5

[rbriga]

How would that work? Suppose the slicer is set in 1-1-2022 : 1-4-2022. How can one move to 2021 without breaking the single year rule?

The measures I previously offered will take the latest year in the selection (Sales CY) and the same period in the previous year (Sales LY) even if the date slicer spans 2+ years. I think it's your best option for a single slicer.

[rbriga]

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!