Forum Discussion
Restart 'count' at certain date
Hello!
I want to restart a 'count,' or 'sum' after a certain date. Here is a screenshot of the table I've created that shows the date, along with some other columns:
The 'Sum Count' column adds the count of the current row, and the count of rows that have an earlier date. Here is the DAX measure for that column:
CALCULATE(
SUMX(Sheet1,[Index]),
FILTER(ALL(Sheet1),
SUMX(FILTER(Sheet1,EARLIER(Sheet1[Month Year]) <= Sheet1[Month Year]),
Sheet1[Msr Count Rows])
)
) Of course, there are other measures referenced: [Index] & [Msr Count Rows]; here are those expressions:
Index =
RANKX(
ALLSELECTED(Sheet1[Month Year]),
CALCULATE(SELECTEDVALUE(Sheet1[Month Year]))
,,1
)
Msr Count Rows = COUNTROWS(Sheet1)Under the 'Msr Sum Count Loop at 13' column, I want a MEASURE that starts over at the row associated with the date of 04/01/2017, and begins the count with 13; the next value under the 'Msr Sum Count Loop at 13' (the row that has a date value of 05/01/2017) should read 27 (which is 13 + 14--the values under the 'Count' column for each respective date).
Here is a link to my .pbix file ===> MY DATA
Here is what I want the values to look like under the 'Msr Sum Count Loop at 13'
Thanks for the consideration. MERRY CHRISTMAS to your & yours!
Hi ccakjcrx
Try this MEASURE
Msr Sum Count Loop at 13 = VAR CurrentCount = [Index] RETURN SUMX ( FILTER ( ALL ( Sheet1[Month Year] ), Sheet1[Month Year] <= SELECTEDVALUE ( Sheet1[Month Year] ) && [Index] > 12 ), CALCULATE ( RANKX ( ALL ( Sheet1[Month Year] ), CALCULATE ( SELECTEDVALUE ( Sheet1[Month Year] ) ), , 1 ) ) )
2 Replies
- Zubair_MuhammadCommunity Champion
Hi ccakjcrx
Try this MEASURE
Msr Sum Count Loop at 13 = VAR CurrentCount = [Index] RETURN SUMX ( FILTER ( ALL ( Sheet1[Month Year] ), Sheet1[Month Year] <= SELECTEDVALUE ( Sheet1[Month Year] ) && [Index] > 12 ), CALCULATE ( RANKX ( ALL ( Sheet1[Month Year] ), CALCULATE ( SELECTEDVALUE ( Sheet1[Month Year] ) ), , 1 ) ) )- ccakjcrxResolver I