cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
megan2010 Frequent Visitor
Frequent Visitor

accumulate count values in time accordance

Capture.PNGHi. I have thousands of row look like this table. This table has data from 2008 and I used a formula below to count the number of the machines upto 2015.

 

Machine Quantity = CALCULATE(COUNT(Table[MachineId]),FILTER(ALL(Table[Delivered]),'Table'[Delivered]<DATE("2015",1,1)))

 

I want to creat a visual that present the add of the number of machines in each month from January 2015 upto most recent month. In each month, there are number of machines delivered therefore I want to show in in month/year accordingly in addition to number of machines in 2015.

 

Can anyone help me to build a formula for this?

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: accumulate count values in time accordance

Hi @megan2010,

 

Please try this measure:

Accumulate count per month =
CALCULATE (
    COUNT ( 'Table'[MachineID] ),
    FILTER (
        ALL ( 'Table'[Delivered] ),
        'Table'[Delivered] > DATE ( 2015, 1, 1 )
            && 'Table'[Delivered] <= MAX ( 'Table'[Delivered] )
    )
)

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
dilumd Established Member
Established Member

Re: accumulate count values in time accordance

Hi @megan2010

 

I'm not sure whether I understood your problem correctly, however I did below change and got into below chart. On the date hierarchy you should remove date and the quarter part.

1.JPG

megan2010 Frequent Visitor
Frequent Visitor

Re: accumulate count values in time accordance

Hi @dilumd. Thanks for your reply.

I don't think I explained my problem correctly. I need to add number of machines every month so it can accumulate on prior month rather than showing how many were added each month. Is there any way I can do that?

dilumd Established Member
Established Member

Re: accumulate count values in time accordance

@megan2010

 

You mean monthly cumulative count?

v-yulgu-msft Super Contributor
Super Contributor

Re: accumulate count values in time accordance

Hi @megan2010,

 

Please try this measure:

Accumulate count per month =
CALCULATE (
    COUNT ( 'Table'[MachineID] ),
    FILTER (
        ALL ( 'Table'[Delivered] ),
        'Table'[Delivered] > DATE ( 2015, 1, 1 )
            && 'Table'[Delivered] <= MAX ( 'Table'[Delivered] )
    )
)

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

UCiccola Frequent Visitor
Frequent Visitor

Re: accumulate count values in time accordance

Hi,

 

I have a similar problem. Could anyone show me a standard dax?
I have this table:

CourseTotal HoursStard DateEnd Date
110001/07/201908/09/2019
25005/03/201908/06/2019
33521/07/201921/09/2019
41515/09/201908/12/2019

 

I need to show accumulative hours from thouse dates. The thing is that if i show this on a visualization, i can only put on X axis only one date, Start or End Date. How can i manage this?

 

Thanks

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 351 members 4,007 guests
Please welcome our newest community members: