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 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.
 

 

Tips and Tricks

4 Replies

  • rbriga's avatar
    rbriga
    Impactful 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!

     

    • rbriga's avatar
      rbriga
      Impactful Individual
      4 years ago

      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.

  • BI_Power_Guy's avatar
    BI_Power_Guy
    Frequent Visitor
    4 years ago

    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.