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 grap...
Need Help
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MartynRamsden,

       

      Thanks for the reply!!

      I think I found the problem. I should use ALLEXCEPT('IB_SEAK HKM_HW','IB_SEAK HKM_HW'[Model]) instead of "ALL" since I have a filter applied on Model.

       



      Thank you!

      Joyce

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Excellent - glad you figured it out!