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.

 

I am able to create a Measure to count the Cumulative count of issues that have been created using the DAX query:

 

Cummulative Raised All Time = 
CALCULATE (COUNTA ('Defects'[Issue key]),
    FILTER (ALL ('Defects'), 'Defects'[Created Date] <= MAX ('Defects'[Created Date]))
)

  

However what I really want to be able to do is compare the rate of tickets raised in 2017 to that of 2018.

 

I have created a Measure that gives me the Cumulative count for 2018 using the following DAX Measure:

 

Cummulative Raised 2018 = 
CALCULATE (COUNTA ('Defects'[Issue key]),
    FILTER (ALL ('Defects'), 'Defects'[Created Date] <= MAX ('Defects'[Created Date])), 'Defects'[Created Date] >= DATE(2018,1,1)
)

  

This works ok and give me the desired results:

Cummulative Raised 2018YearMonth
222018January
442018February
742018March
1172018April
1402018May
1572018June
1712018July

 

However what I can't seem to be able to do is produce a query that give me only the 2017 data. The best I can come up with is this Measure:

Cumulative Count 2017 = CALCULATE(COUNTA('Defects'[Issue key]), 
FILTER (ALL ('Defects'), 'Defects'[Created Date] <= MAX ('Defects'[Created Date])),
DATESBETWEEN('Defects'[Created Date],DATE(2017,1,1),DATE(2017,12,31)))

But that gives me this:

Cumulative Count 2017YearMonth
272017January
542017February
892017March
1132017April
1402017May
1662017June
1762017July
1892017August
2012017September
2212017October
2442017November
2552017December
2552018January
2552018February
2552018March
2552018April
2552018May
2552018June
2552018July

 

Can anybody help we define a measure that will only return 2017 data?

 

Thanks in advance

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

3 Replies

  • 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

  • RB16kb's avatar
    RB16kb
    Frequent Visitor
    8 years ago

    The image below is a representation of what it is I am looking to acheive.

     

    The more I think about it the more I suspect I need to create a custom table based on the data I am supplied. In otherwords the  needs to be formated in to a table like that below: 

     201620172018
    Jan442021
    Feb804038
    Mar1056865
    Apr13287103
    May166107126
    Jun196127139
    Jul227136152
    Aug245149 
    Sep265159 
    Oct284172 
    Nov312193 
    Dec333204 

     

    Sample Data

    Issue IDCreated DateResolved Date
    AAA-075803/01/201713/04/2017
    AAA-075904/01/201719/05/2017
    AAA-077801/02/201716/08/2017
    AAA-077906/02/201715/02/2017
    AAA-078006/02/201702/05/2017
    AAA-079801/03/201708/11/2017
    AAA-079902/03/201714/03/2017
    AAA-080006/03/201728/03/2017
    AAA-082604/04/201705/06/2017
    AAA-082704/04/201701/09/2017
    AAA-082804/04/201722/01/2018
    AAA-084502/05/201715/11/2017
    AAA-084603/05/201727/07/2017
    AAA-084703/05/201730/11/2017
    AAA-084808/05/201709/07/2018
    AAA-084915/05/201721/07/2017
    AAA-086505/06/201703/07/2017
    AAA-086609/06/201727/07/2017
    AAA-086712/06/201710/07/2017
    AAA-086813/06/201706/09/2017
    AAA-086913/06/201720/10/2017
    AAA-087014/06/201719/06/2017
    AAA-087114/06/201726/06/2017
    AAA-088507/07/201703/08/2017
    AAA-088607/07/201715/08/2017
    AAA-089401/08/201706/09/2017
    AAA-089501/08/201726/09/2017
    AAA-089604/08/201720/04/2018
    AAA-089707/08/201721/08/2017
    AAA-089808/08/201724/08/2017
    AAA-089908/08/201703/11/2017
    AAA-090711/09/201721/09/2017
    AAA-091701/10/201710/10/2017
    AAA-091804/10/201709/11/2017
    AAA-091906/10/201729/01/2018
    AAA-092006/10/201712/02/2018
    AAA-092109/10/201708/11/2017
    AAA-092209/10/2017 
    AAA-092310/10/201713/11/2017
    AAA-093001/11/2017 
    AAA-093102/11/201717/11/2017
    AAA-093203/11/201713/11/2017
    AAA-095105/12/201711/01/2018
    AAA-095205/12/201730/04/2018
    AAA-095306/12/201707/12/2017
    AAA-095407/12/201718/12/2017
    AAA-096204/01/201829/01/2018
    AAA-096309/01/2018 
    AAA-096409/01/2018 
    AAA-096510/01/201815/03/2018
    AAA-096611/01/201807/02/2018
    AAA-096711/01/201826/04/2018
    AAA-096812/01/2018 
    AAA-098301/02/201820/04/2018
    AAA-098401/02/2018 
    AAA-098504/02/201819/02/2018
    AAA-098605/02/201809/03/2018
    AAA-098706/02/2018 
    AAA-098807/02/201808/03/2018
    AAA-098907/02/2018 
    AAA-099009/02/201814/03/2018
    AAA-100005/03/201808/03/2018
    AAA-100107/03/201817/03/2018
    AAA-100208/03/201813/03/2018
    AAA-100308/03/201814/03/2018
    AAA-100408/03/201819/03/2018
    AAA-100508/03/201822/03/2018
    AAA-100608/03/201823/03/2018
    AAA-100708/03/2018 
    AAA-100808/03/2018 
    AAA-100909/03/2018 
    AAA-101012/03/201816/04/2018
    AAA-101113/03/201822/03/2018
    AAA-101213/03/201826/03/2018
    AAA-101313/03/201828/03/2018
    AAA-102703/04/2018 
    AAA-102804/04/201805/04/2018
    • Anonymous's avatar
      Anonymous
      Not applicable
      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