Forum Discussion

Ben1981's avatar
Ben1981
Helper III
1 year ago
Solved

Aggregate Sum with specific dates

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 Custo...
Need Help
  • dk_dk's avatar
    dk_dk
    1 year ago

    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's avatar
rajasaadk_98
Helper I
1 year ago

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
)
)