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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
wynhodgkiss
Helper III
Helper III

SAMEPERIODLASTYEAR with rolling sum

Hi,

I have a sales measure that is then dispalyed as MTH, RQ or MAT:

SalesFinal =
var _TType = SELECTEDVALUE(TimeAggregation[Time Aggregation])
RETURN
SWITCH(TRUE(),
_TType = "MTH", [Sales],
_TType = "RQ", SUMX(DATESINPERIOD(DatesTable[Month],LASTDATE(DatesTable[Month]),-3,MONTH),[Sales]),
_TType = "MAT", SUMX(DATESINPERIOD(DatesTable[Month],LASTDATE(DatesTable[Month]),-12,MONTH),[Sales]),0)
 
all good, works fine but I'm a bit stuck trying to use SAMEPERIODLASTYEAR with the above measure. 
 
What I have is:
 
SalesGrowth =
var _PY = CALCULATE([SalesFinal], SAMEPERIODLASTYEAR(DatesTable[Month]))
RETURN
SWITCH(TRUE(),
_PY = 0, "",
[SalesFinal]-_PY)
 
but this only works for MTH. I'd like a measure that calculates SalesFinal for the same period last year ONLY when all months in that period have values. Happy to go back to the drawing board on either measure if I'm getting something wrong, any help would be appreciated cheers
1 ACCEPTED SOLUTION
rohit1991
Super User
Super User

Hi @wynhodgkiss ,

 

This is a pretty common scenario when working with rolling periods and year-on-year comparisons in Power BI. The trick is to make sure you only calculate the prior year rolling sum (e.g., MAT, RQ, etc.) when all the months in that prior year window have data. Otherwise, you risk showing misleading numbers due to missing months. Here’s how you can do it for MAT (12 months), but you can adjust the window for RQ or other periods as needed:

 

SalesFinal_PY_MAT =
VAR SelectedDate = MAX(DatesTable[Month])
VAR PY_Window =
    DATESINPERIOD(DatesTable[Month], EDATE(SelectedDate, -12), 12, MONTH)
VAR AllMonthsPresent =
    CALCULATE(
        COUNTROWS(DatesTable),
        PY_Window
    ) = 12 // Change 12 to 3 for rolling quarter, etc.
RETURN
IF(
    AllMonthsPresent,
    CALCULATE([SalesFinal], PY_Window),
    BLANK()
)

 

This checks if there are 12 months of data in the prior year window, and only returns a value if all are present otherwise, it stays blank until you have a full set. You can apply the same pattern for rolling quarters or custom periods just update the window size.

 


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

View solution in original post

6 REPLIES 6
wynhodgkiss
Helper III
Helper III

Once I'd changed my dates table to monthly and made a slight adjustment to the code (the intervals needed to be negative too) this worked perfectly. Thanks so much for your help.

wynhodgkiss
Helper III
Helper III

That's working until the COUNTROWS part. If I just run that line without the = 12 I get a result of 395. Is that because my dates table has a row per day and if so how do I adjust the code to allow for this?

rohit1991
Super User
Super User

Hi @wynhodgkiss ,

 

This is a pretty common scenario when working with rolling periods and year-on-year comparisons in Power BI. The trick is to make sure you only calculate the prior year rolling sum (e.g., MAT, RQ, etc.) when all the months in that prior year window have data. Otherwise, you risk showing misleading numbers due to missing months. Here’s how you can do it for MAT (12 months), but you can adjust the window for RQ or other periods as needed:

 

SalesFinal_PY_MAT =
VAR SelectedDate = MAX(DatesTable[Month])
VAR PY_Window =
    DATESINPERIOD(DatesTable[Month], EDATE(SelectedDate, -12), 12, MONTH)
VAR AllMonthsPresent =
    CALCULATE(
        COUNTROWS(DatesTable),
        PY_Window
    ) = 12 // Change 12 to 3 for rolling quarter, etc.
RETURN
IF(
    AllMonthsPresent,
    CALCULATE([SalesFinal], PY_Window),
    BLANK()
)

 

This checks if there are 12 months of data in the prior year window, and only returns a value if all are present otherwise, it stays blank until you have a full set. You can apply the same pattern for rolling quarters or custom periods just update the window size.

 


Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
mwjansen
Helper I
Helper I

Sorry I misunderstood. Even more sorry I don't have a solution for your issue. 

wynhodgkiss
Helper III
Helper III

that doesn't help me as I am not looking for YTD. 

 

This is my SalesFinal measure, the values controlled by an unconnected table.

 

MonthSalesFinal MTHSalesFinal RQSalesFinal MAT
jun 202550016006000
etc   

 

The measure I need should calculate SalesFinal for the relevant period of the previous year IF there is data for all months in the period so for example:

MonthSalesFinal_PY MTHSalesFinal_PY RQSalesFinal_PY_MAT
june 202540015005800

but as there is no data before Jan 2022:

MonthSalesFinal_PY MTHSalesFinal_PY RQSalesFinal_PY_MAT
Jan 202320000
Feb 202330000
Mar 20234009000

etc... 

SalesFinal_PY_MAT should not be available until 12 months of data exists (Dec 2023)

mwjansen
Helper I
Helper I

  @wynhodgkiss

 

YOY =

// Filter year-to-date and prior year-to-date

var _today= TODAY()
RETURN
UNION(
    ADDCOLUMNS(
        CALENDAR( DATE(YEAR(_Today),1,1), _today)
    //    , "Filter", FORMAT(_today,"YTD")
        , "Filter", "YTD"
           
    ),ADDCOLUMNS(
        CALENDAR( DATE(YEAR(_Today) -1,1,1), _today - 365)
        , "Filter", "Prior YTD"
       
    ))
 
Relationship to existsing table, then use "Filter" for last year-to-date. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.