Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Problems with cumulative total in column chart

Hello

 

I'm experiencing a weird behaviour using a cumulative total wiht the following formula:

Cumulative_actual_1 = 
CALCULATE (
    DISTINCTCOUNT ( 'ChangeStatus (2)'[issueid] );
    FILTER (
        ALLEXCEPT (  'ChangeStatus (2)';  'ChangeStatus (2)'[Status]);
         'ChangeStatus (2)'[Month] <= MAX (  'ChangeStatus (2)'[Month] ) 
    )
)

The problem is when I display the formula in a column chart:

As you can see I have 613 on the chart but in fact the total is 219, the chart is filter to only display the year 2017 and the AllEXcept is to ensure I can show the count by status.

 

How can I solve this?

 

Thanks

Need Help

8 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Anonymous,

     

    Please try to use the DAX below.

    Cumulative_actual_1 = 
CALCULATE (
    DISTINCTCOUNT ( 'ChangeStatus (2)'[issueid] );
    FILTER (
        ALLEXCEPT (  'ChangeStatus (2)';  'ChangeStatus (2)'[Status]);
         'ChangeStatus (2)'[Month] <= EARLIER(  'ChangeStatus (2)'[Month] ) 
    )
)

     

    Regards,

    Charlie Liao 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi Anonymous,

     

    Here's what i would do:

     

    1. Create a calendar table which should have running dates from the first date in the date column of your ChangeStatus (2) table to the last date in that date column of your ChangeStatus (2) table.
    2. In the calendar table, extract month using =FORMAT(Calendar[Date],"mmmm")
    3. In the calendar table, extract year using =YEAR(Calendar[Date]) 
    4. Create a relationship from the Date column of your ShangeStatus (2) table to the date column of your calendar table
    5. Drag Year to the filter section of your visual and select 2017
    6. Drag Month to the X-axis of your column chart
    7. Write this calculated field formula
    =CALCULATE(DISTINCTCOUNT('ChangeStatus (2)'[issueid]),DATESYTD(Calendar[Date],"31/12"))

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Ashish_Mathur

       

      Thanks for the reply. Your aproach in fact did not work because I have complete dates but the calendar gives "only" the days.. so the relation does not work.

       

      v-caliao-msft

       

      Thanks for the reply, but it gave me even higher values... :( I was using a measure, but in your solution I had to create a column to work it Earlier.

       

      this seems a simple calculation.. but.. maybe my data model is not standard.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi Anonymous,

         

        Share the link from where i can download your workbook.