Forum Discussion

MSAYED26's avatar
MSAYED26
Helper II
1 year ago
Solved

Accumulative count

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.

"DAX " "DAX help" 'Need help'
  • Anonymous's avatar
    Anonymous
    1 year ago

    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!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!