Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MSAYED26
Helper II
Helper II

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 :MSAYED26_0-1740057673522.png

I right result in excel : 

MSAYED26_1-1740057739616.png

Thanks.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

vjingzhanmsft_1-1740125299700.png

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. 

vjingzhanmsft_0-1740125138494.png

 

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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. 

vjingzhanmsft_1-1740125299700.png

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. 

vjingzhanmsft_0-1740125138494.png

 

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

MSAYED26
Helper II
Helper II

The Data :

MSAYED26_0-1740058242685.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.