How to use Rolling 12 month with Sum DAX and ifs DAX

[Anonymous]

Hi,

Can anyone help me here 

i have 3 table Master, FY2324 and FY2425, 

i used Append function and merged FY2324 and FY2425, in Master , here i need to use Rolling12 month

(RAG12Month = CALCULATE(SUM(Customer[Achievement]),DATESINPERIOD(Customer[Dated], MAX(Customer[Dated]), -12, MONTH)))

  So now i need to sum based on NewCRMID the below image 4 id will be there Ie. 

NEW CRM ID	SBU	Dated	Achievement	RAGTOTALVal (Rolling 12 month)
3871				340380
3871	PWR	20-04-2023	6467	340380	Blue will get filter bcz its will excited 12 month
3871	PWR	15-11-2023	230380	340380
3871	SECS	09-05-2024	110000	340380

and then i have to 

1. Rolling 12 month.

2. based on the NEW CRM ID, get the Values

[Anonymous]

Hi @Anonymous ,

Thanks @Greg_Deckler  for the quick reply and solution. Allow me to offer some other insights:

You can create a column.

RAGTOTALVal (Rolling 12 month) = var _a=CALCULATE(SUM(Customer[Achievement]),FILTER('Customer',[NEW CRM ID]=EARLIER('Customer'[NEW CRM ID])))
RETURN CALCULATE(_a,DATESINPERIOD(Customer[Dated], MAX(Customer[Dated]), -12, MONTH))

Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hi Tangjie, 

i'm getting the below error

The MAX function only accepts a column reference as an argument.

[Anonymous]

Hi @Anonymous ,

Are you creating a calculated column?

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Yes Tangjie

[Anonymous]

Hi @Anonymous ,

Under my tests it worked fine. Can you provide a PBIX file (please be careful to protect private data).

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

[Greg_Deckler]

@Anonymous Try this: Better Rolling Average - Microsoft Fabric Community Just replace AVERAGEX with SUMX