Forum Discussion

RB16kb's avatar
RB16kb
Frequent Visitor
8 years ago
Solved

Cumulative Count of Rows between for a Given Date Range

Hello there, I track the number of defects that are reported by our customers and, amongst other things, I want to be able to track the cumultive number of tickets that have been reported over time....
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi RB16kb,

     

    You can create a expand table to expand date range from original table, then use expand table to create visual:

    Table =
VAR maxdate =
    MAXX ( Table2, [Resolved Date] )
VAR _Calendar =
    CALENDAR (
        MIN ( Table2[Created Date] ),
        IF ( maxdate > TODAY (), maxdate, TODAY () )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Table2, _Calendar ),
            Table2[Created Date] <= [Date]
                && IF ( Table2[Resolved Date] <> BLANK (), Table2[Resolved Date], TODAY () )
                    >= [Date]
        ),
        "Issue ID", [Issue ID],
        "Created Date", [Created Date],
        "Resolved Date", [Resolved Date],
        "Detail Date", [Date]
    )

     

    Spread revenue across period based on start and end date, slice and dase this using different dates


    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI RB16kb,

 

According to your description, it seems like you have confused to write a formula to calculate cumulative count on 2017 records, maybe you can try to use below formula:

Cumulative Count 2017 =
CALCULATE (
    COUNTA ( 'Defects'[Issue key] ),
    FILTER (
        ALL ( 'Defects' ),
        'Defects'[Created Date] <= MAX ( 'Defects'[Created Date] )
            && YEAR ( 'Defects'[Created Date] ) = 2017
    )
)

If above not help, can you please share some sample data for testing?

 

Regards,

Xiaoxin Sheng