Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Morgan2000
Frequent Visitor

Comparing Rolling Years

Hi!

 

I have two measures to work out firstly PAX rolling year 

PAX Rolling Year = 
VAR ReferenceDate = MAX(A[Period])
VAR PreviousDates =
    DATESINPERIOD(
        'Previous Date'[Date],
        ReferenceDate,
        -1,
        YEAR
    )
VAR Result =
    CALCULATE(
       [Passengers Sum A],
        REMOVEFILTERS('Calendar'),
        KEEPFILTERS(PreviousDates),
        USERELATIONSHIP('Calendar'[Date], 'Previous Date'[Date])
    )
RETURN
    Result

And also one that works out 'Comparison Rolling Year' is to go in line with two slicers I have. If the slicer select has a maximum month date that is 6 (June) for example it will still give 12 months of data (rolling 12 months part) and comparison is the same  however based of a different slicer.

Comparison PAX Rolling Year = 
VAR ReferenceYEAR = YEAR(MAX('Comparison Calendar'[Date]))
VAR ReferenceMonth = MONTH(MAX(A[Period]))
--VAR ReferenceDate = DATE(ReferenceYEAR, ReferenceMonth, 1)
VAR ReferenceDate =
    IF(
        MONTH(MAX('Comparison Calendar'[Date])) = 12,
        DATE(YEAR(MAX('Comparison Calendar'[Date])) + 1, 1, 1),
        DATE(YEAR(MAX('Comparison Calendar'[Date])), MONTH(MAX('Comparison Calendar'[Date])) + 1, 1)
    )
VAR PreviousDates =
    DATESINPERIOD(
        'Previous Date'[Date],
        ReferenceDate,
        -1,
        YEAR
    )
VAR Result =
    CALCULATE(
       [Comparison Pax A],
        REMOVEFILTERS('Comparison Calendar'),
        KEEPFILTERS(PreviousDates),
        USERELATIONSHIP('Comparison Calendar'[Date], 'Previous Date'[Date])
    )
RETURN
    Result 

 

These are working perfectly in some ways, 

Morgan2000_0-1694704053703.png

It is giving the results I am expecting and works for adding however, I am trying to do a percentage difference between the two. This works when there is no year context however when adding the year it doesn't work. I want year context to be able to put it on a graph with month along the bottom and the months to be in the correct order.

 

What I need assistance with is how can I get the % difference to comparison to show on the same line as the pax rolling year.

 

so in the example above I want all the July figures to show on the 2022 line. If I could get the comparison pax to that level the % would also update.

 

Normally I would use dateadd but with the rolling year I am getting confused. Any help would be great 😄

 

 

1 REPLY 1
lbendlin
Super User
Super User

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.