Forum Discussion

cazza's avatar
cazza
Frequent Visitor
1 year ago
Solved

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

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
    )
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

6 Replies

  • Ashish_Excel's avatar
    Ashish_Excel
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hi,

    Does this pattern work?

    Measure = sum(Data[Value])

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

    • cazza's avatar
      cazza
      Frequent Visitor
      1 year ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.