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.


Muhammad_Ahmed's avatar
Muhammad_Ahmed
Helper I
1 year ago

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's avatar
dk_dk
Super User
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.