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

[ERing]

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 example, a user selects 28 days (12/6/2024 - 1/2/2025), I need to populate the Inbound Calls Conversion Rate = (INBOUND_CALLS / PAGE_VIEWS) for the current period 12/6/2024 - 1/2/2025 AND the same conversion rate for the SAME period last year (12/6/2023 - 1/2/2024).



SAMPLE DATA 

[ERing]

@Uzi2019 @powerbiexpert22 Thank you for your help. I could also use help on one more measure.

I have a requirement to show a comparison period that is equal to the same number of days prior to what is selected in the relative date calendar selection.

For example, suppose a user selects 7 days in the relative date selection. I need to show the Inbound Calls Conversion Rate = (INBOUND_CALLS / PAGE_VIEWS) for the period equal to the prior 7 days (days 8 - 14) as well.

Please let me know if you can assist. 

[Uzi2019 · Accepted solution]

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]

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)