Problems with cumulative total in column chart

[Anonymous]

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

[Ashish_Mathur]

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]

@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]

Hi @Anonymous,

Share the link from where i can download your workbook.

[Anonymous]

 Hi @Ashish_Mathur

Let me work on one to be able to send you. The one I have I can not send it.

I will post the link after that.

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

See my solution here.

Hope this helps.

[Anonymous]

Thanks @Ashish_Mathur I used your solution and manage to make it work on my pbix.

The problem is that created was a datetime type, but in order to work I had to change it for a Date type. I think this happens because calendar uses Date and not datetime as the date field.

[Ashish_Mathur]

You are welcome.

[v-caliao-msft]

@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