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

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!

     

3 Replies

  • 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)
     

     

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    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!

     

  • ERing's avatar
    ERing
    Post Partisan
    1 year ago

    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.