Forum Discussion

ERing's avatar
ERing
Post Partisan
1 year ago
Solved

Dynamic Year Over Year Using Relative Date Slicer (Sample Data)

I need to provide year over year data based on a relative date slicer. The goal is to show the same period for the prior year depending on what number of days is selected in the date slicer. For e...
Need Help
  • Uzi2019's avatar
    Uzi2019
    1 year ago

    Hi ERing 

     

    check this below pbix .

     

    I have done all the calculation and getting the same period last year conv rate.

     

    I have worked on your sample file.

     

    I hope I answered your question!

     

powerbiexpert22's avatar
powerbiexpert22
Impactful Individual
1 year ago

Hi ERing ,

 

please create two variables for selected period and previous year period as shown below . please see below pbix file for your reference

https://drive.google.com/file/d/1DZy1ki3xcHLgHZfQ152WeFpZZ639i-6Y/view?usp=drive_link

 

 

conv rate selected period =
    var inbound_calls = sum(Inbound_Calls[INBOUND_CALLS])
    var page_views = sum(Web_Data[PAGE_VIEWS])
    RETURN
DIVIDE(inbound_calls,page_views)

 

conv rate prev year period =
var start_date=EDATE(MIN('Calendar'[Date]),-12)
var end_date= EDATE(TODAY(),-12)
var inbound_call_last_year=
CALCULATE(
    SUM(Inbound_Calls[INBOUND_CALLS]),
    FILTER(all('Calendar'[Date]),'Calendar'[Date]>=start_date
    && 'Calendar'[Date]<=end_date
    )
)
 var page_views_last_year=
CALCULATE(
    SUM(Web_Data[PAGE_VIEWS]),
    FILTER(all('Calendar'[Date]),'Calendar'[Date]>=start_date
    && 'Calendar'[Date]<=end_date
    )  
)
return DIVIDE(inbound_call_last_year,page_views_last_year)