Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Cumulative Total

Hi All,
 
I want to come up with a graph that shows the cumulative orders for years that has orders. For example,there were 2 orders in 1996 , 0 in 1997 and 1 order in 1998.  So ideally, on a graph, for the year 1998, there would be 3 orders(inclusive of the 2 orders in 1996).  I found this formula from the forums that suggested that this would allow me to get the cumulative number, however, it doesnt seem to be working since i don't get the correct number.
 
This is the formula that I used: 
Target = CALCULATE(COUNT('IB_SEAK HKM_HW'[PCSN]),Filter(ALL('IB_SEAK HKM_HW'),'IB_SEAK HKM_HW'[Date Installed].[Date]
<=MAX('IB_SEAK HKM_HW'[Date Installed].[Date])))
 

This is what I get:

 

The table on the right is an accurate summary of the data. As you can see, the graph incorrectly shows that there are 3 orders for the year 1997 when there are actually no orders at all. There shouldnt have year 1997 in the graph. This inaccuracy is reflected for all the years displayed in the graph.


Does anyone know what's wrong with the formula? Or why it has gone awry?

 

Thanks very much

Need Help

8 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    6 years ago

    Hi Anonymous 

     

    Try this:

     

    Target = 
CALCULATE (
    COUNT ( 'IB_SEAK HKM_HW'[PCSN] ),
    FILTER (
        ALL ( 'IB_SEAK HKM_HW' ),
        'IB_SEAK HKM_HW'[Date Installed] <= MAX ( 'IB_SEAK HKM_HW'[Date Installed])
    )
)

     

    Best regards,

    Martyn

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MartynRamsden,

      I tried this and i still don't get the accurate result. 

      This is what I got: 

       

      If you refer to the table that I provided in the original post, you'd see that the number is correct for both 1996 and 1997 but is inaccurate for the rest of the years. For example, for 1998, the number should be 2+0+ 1 = 3 but the measure that you suggested renders 5 instead.

       

      Thanks for the suggestion though! Would you happen to know why the rest of the years are wrong? 

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Hi Anonymous 

         

        I think I understand why that didn't work - are you using a date dimension table?

        If so, try this:

         

        Target = 
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR Result = 
CALCULATE (
    COUNTROWS( 'IB_SEAK HKM_HW' ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] <= MaxDate)
    )

RETURN 
Result

         

        Best regards,

        Martyn