Sum per month

[Anonymous]

Hi, 

I have the following table:

 

I would like to get the sum of the values in the graph instead of the values, so for February it should be 7 and for March it would be 9.

Any tips here?

Thanks for any help. 

[jsh121988]

I'm not sure where you're getting your example output numbers. You may just need to select Count instead of Distinct Count from the values dropdown.

A measure would be better:

MyMeasure = // Remove the // from one of the below based on your need
// COUNTROWS(MyTable) // Use this to sum 1 for each row.
// DISTINCTCOUNT(MyTable[AccountId]) // Use this to sum 1 for each unique Id
// SUM(MyTable[SomeNumberField]) // Use this to sum a particular field

Drag the measure to the values section on the visual.

[Anonymous]

Thanks for your response. So there are 2 values for January. And then 5 values for February. I would then like the total for February to be 2 + 5. For March there are also 2 values, the total in March should be 2 + 5 +2. 

I tried what you suggested but it doesn't give me required result. 

Example: 7 in total for February and then 9 in total for March.

Thank you.

[Anonymous]

I also tried using the cumulative formula: 

[SeanDonovan]

Hello,

I've made some modifications to your cumulative formula.  Let me know if this works:

Cumulative_Actual =
CALCULATE (
    SUM ( 'Accounts'[accountid] ),
    FILTER (
        ALL ( 'Accounts' ),
        'Accounts'[Date] <= MAX ( 'Accounts'[Date] )
    )
)

[Anonymous]

Hi, 

Thanks Sean but I dont think I have the answer I want. I want to be able to cumulatively add the amounts for the accountid per month. So for January it's 2, for February it should be 7(2 + 5) and for March it would be 9 (2+5+2).

Can you advise what's the best way to get this?

Cheers

[Anonymous · Accepted solution]

Do you have a dedicated Date Table?  If so, you can leverage the builtin time-intelligence functions to give you Total Year to Date of a measure:

 TOTALYTD MEASURE=
/*-------------------------------------------------------------------------
            'Simple calculation when do not need further complex filtering'
            'Use the Date/Calendar Table and Date Key'
            'Be sure that the Date Table is set as a date table'
--------------------------------------------------------------------------*/
 TOTALYTD ( [MEASURE] , Date[DateKey] ',Optional Fiscal Year End')

If you want to use a column, this code will work:

Cumulative Total Based on Date = 
VAR __CurrentDate= Table1[Date]
RETURN

CALCULATE(
    COUNTROWS( Table1 ),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date]
    )
)

[Anonymous]

Hi Nick,

That did the job, thank you. I have one more question. I have a column called "order cancelled", it's a date column. 

For example in your table if order 4 was cancelled on February 20, how could you filter this out?

Thanks again

[Anonymous]

This will only continue the count if the date canceled isblank:

Cumulative Total Based on Date = 
VAR __CurrentDate= Table1[Date Ordered]
RETURN

CALCULATE(
    COUNTROWS( Table1 ),
    FILTER(
        ALL( Table1),
        __CurrentDate >= Table1[Date Ordered]
            && ISBLANK( Table1[Date Cancelled] )
    )
)

I think that is what you had in mind?