Forum Discussion
Sum per month
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.
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] ) )
8 Replies
jsh121988Microsoft Employee
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.
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.