Forum Discussion
Sum per month
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] ) )
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.
I also tried using the cumulative formula:
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] )
)
)
