Time Series Chart Showing Selected Data and Prior Period based on Slicer

[cazza]

Hi all,

I'm trying to get have a time series graph show the Current Period (slicer selection) and the Prior Period (which is one year prior, based on the slicer selection).

I have managed to get a measure that shows what I want in aggregate but it falls down when using it against a date in the graph because it ignores the time filters.  I'm deliberately wanting to show one year prior based on the date range selected e.g. if 2025-01-01 to 2025-01-31 is selected, the Prior Year shown (but not selected in the slicers) should be 2024-01-01 to 31-01-31.  The values below check out, I'm just not sure how to not have the aggregated value show as a straight line on the graph.

My measure for Send Email 1YP = 

VAR min_date = CALCULATE(MIN('Calendar'[Date]), ALLSELECTED('Marketing - Marketo - Overview Engagement Metrics'))

VAR max_date = CALCULATE(MAX('Calendar'[Date]), ALLSELECTED('Marketing - Marketo - Overview Engagement Metrics'))

VAR min_date_1YP = IF(ISBLANK(min_date), BLANK(), DATE(YEAR(min_date)-1, MONTH(min_date), DAY(min_date)))

VAR max_date_1YP = IF(ISBLANK(max_date), BLANK(), DATE(YEAR(max_date)-1, MONTH(max_date), DAY(max_date)))

RETURN

    CALCULATE(

        'Marketing - Marketo - Overview Engagement Metrics'[Send Email],

        REMOVEFILTERS('Calendar'[Date], 'Calendar'[Year]),

            'Marketing - Marketo - Overview Engagement Metrics'[activity_date] >= min_date_1YP &&

                'Marketing - Marketo - Overview Engagement Metrics'[activity_date] <= max_date_1YP

    )

[v-priyankata · Accepted solution]

Hello @cazza 
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have successfully reproduced your scenario in Power BI and I was able to get the expected output as per your requirement where:

• The current period's data (based on slicer selection) is shown, and
• The prior year's data for the same relative period (one year back) is calculated and displayed side-by-side in the table.

This allows you to clearly compare performance between the selected time window and the equivalent period from the previous year.

For your reference, I’m attaching the .pbix file so you can explore the solution in detail.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-priyankata]

Hi @cazza 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-priyankata]

Hi @cazza 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so other members can easily find it.
Thank you.

[v-priyankata]

Hi @cazza 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Ashish_Excel]

Hi,

Does this pattern work?

Measure = sum(Data[Value])

Measure1 = calculate([Measure],sameperiodlastyear(calendar[date]))

[cazza]

Hi,

I also have a scenario which is pretty much identical to the above except for the preceding period.  I have managed to get variables to give me the answer but still have the same issue trying to plot it on a graph.