Aggregate Sum with specific dates

[Ben1981]

Having trouble with what should be a simply DAX measure but I can't see what I'm doing wrong, I'm trying to achieve the below...

I have a static table with a few columns in such as Month and Customer Count and Data Type.

I want to do a sum of the customer Count when the data type is "current" but a sort of rolling 4 month aggregate. So the measure will do a SUM of customer count for each month back dated 4 months from the current month but only when the data type is current.

So an example below, for the month of 01/12/2024, I'm essentially doing a SUM of the count from 01/09/2024 to 01/09/2024 and then the next month I'll do a SUM of 01/01/2025 from 01/10/2024.

[Muhammad_Ahmed]

Hi, 
Thank you for sharing your problem.
Can you try it out.
Rolling 4-Month Cust Count =
CALCULATE(
SUM('Table'[Cust Count]),
'Table'[Data Type] = "Current",
DATESINPERIOD(
'Table'[Month],
MAX('Table'[Month]),
-4,
MONTH
)
)

[dk_dk · Accepted solution]

Hi @Ben1981 , @Muhammad_Ahmed 

This solution has the right idea, but you are missing an ALL from th filter conditions.

See below my adjusted measure:

NewMeasure = 

VAR currMonth = SELECTEDVALUE('Table'[Month])
VAR rolling4months = DATESINPERIOD('Table'[Month],currMonth,-4,MONTH)


RETURN
CALCULATE(SUM('Table'[Cust Count]),ALL(),'Table'[Data Type]="Current",'Table'[Month] IN rolling4months)

and a table comparing the results:

"Measure" is the reference column from the example table provided. NewMeasure is my measure.

[rajasaadk_98]

To create a rolling 4-month aggregate of the customer count in Power BI (DAX), where you only sum the Customer Count for months classified as "Current", you can use the CALCULATE function along with FILTER to control the time range and data type.

Here's the DAX measure that will accomplish this:
Rolling4MonthCustomerCount =
CALCULATE(
SUM('YourTable'[Cust Count]),
FILTER(
'YourTable',
'YourTable'[Month] <= EARLIER('YourTable'[Month]) &&
'YourTable'[Month] >= EDATE(EARLIER('YourTable'[Month]), -3) && -- Going back 3 months (4 months total)
'YourTable'[Data Type] = "Current" -- Only include "Current" data type
)
)