Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I have a sales measure that is then dispalyed as MTH, RQ or MAT:
Solved! Go to Solution.
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.
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.
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?
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.
Sorry I misunderstood. Even more sorry I don't have a solution for your issue.
that doesn't help me as I am not looking for YTD.
This is my SalesFinal measure, the values controlled by an unconnected table.
Month | SalesFinal MTH | SalesFinal RQ | SalesFinal MAT |
jun 2025 | 500 | 1600 | 6000 |
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:
Month | SalesFinal_PY MTH | SalesFinal_PY RQ | SalesFinal_PY_MAT |
june 2025 | 400 | 1500 | 5800 |
but as there is no data before Jan 2022:
Month | SalesFinal_PY MTH | SalesFinal_PY RQ | SalesFinal_PY_MAT |
Jan 2023 | 200 | 0 | 0 |
Feb 2023 | 300 | 0 | 0 |
Mar 2023 | 400 | 900 | 0 |
etc...
SalesFinal_PY_MAT should not be available until 12 months of data exists (Dec 2023)
User | Count |
---|---|
78 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
104 | |
93 | |
52 | |
50 | |
46 |