Forum Discussion

ccakjcrx's avatar
ccakjcrx
Resolver I
8 years ago
Solved

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_Muhammad's avatar
    Zubair_Muhammad
    Community 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
                )
            )
        )