Accumulative count

[MSAYED26]

Hi,

I want to calculate accumulative in matrix depond on two date (rows is return date , columns is production date ) ,accumulative relative to return date and sort it by production date .

i use this measures: 

1-DISTINCTCOUNT(Total[SAP Number])

2-

ACCumlative =

CALCULATE(

    [Total Order Claims2],

    FILTER(

        ALLSELECTED(Total),

        Total[Month of Return].[Year] >= DATE(YEAR(MAX(Total[Month of Return].[Year])),1,1) &&

        Total[Month of Return].[Year] <= MAX(Total[Month of Return].[Year]) &&

        Total[Date production.].[Year] <= MAX(Total[Date production.].[Year])

    )

)

and this is result :

I right result in excel : 

Thanks.

[Anonymous · Accepted solution]

Hi @MSAYED26 

You can add a new table for Production Year and make it disconnected with 'Total' table. Use it on columns of matrix visual. 

Then create measures

New Total = 
VAR vProductYear = MAX('Production Year'[Year])
RETURN
CALCULATE([Total Order Claims2], Year(Total[Date Production])=vProductYear)

New Accu = 
VAR vProductYear = MAX('Production Year'[Year])
VAR vReturnYear = YEAR(MAX(Total[Month of Return]))
RETURN
CALCULATE([Total Order Claims2], FILTER(ALLSELECTED(Total), YEAR(Total[Date Production])=vProductYear && YEAR(Total[Month of Return])>=vProductYear && YEAR(Total[Month of Return])<=vReturnYear ))

Here is my result. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[MSAYED26]

The Data :