Summarize Table by Month and Year.

[pafernandes]

Hi. I'm trying to create a summary table that group YY-AAAA and category.

Table data

Table Date

Problem
When I try calculate the number of tickets close in May, the formulate get only five tickets, but the correct it's 10. five it's the quantity of tickets close in May but open in May.

Resumo Ticket's II = 
var dim_calenddario  = 
    SUMMARIZE(            
        dCalendario
        ,dCalendario[AnoMes]
        ,dCalendario[Ano]
        ,dCalendario[Mês]
        ,"First Day" , MIN(dCalendario[Date]))
        
 var dim_cross = CROSSJOIN(dim_calenddario,Tipo_Tickets)

 var dim_dados = SUMMARIZE(
                    dim_cross
                   ,[AnoMes]
                   ,[Tipo]
                   ,[First Day]
                   , 
                    "Chamados criados" ,
                        CALCULATE(
                        COUNT('Controle de Ticket´s BI (2)'[ID Ticket]), 
                        ALL('Controle de Ticket´s BI (2)'[Ticket Mtec: Data de criação]),
                        FILTER('Controle de Ticket´s BI (2)', 'Controle de Ticket´s BI (2)'[Ticket Mtec.Tipo_Ticket__c] = [Tipo]),
                        FILTER('Controle de Ticket´s BI (2)', FORMAT([Ticket Mtec: Data de criação], "MM-YYYY" ) = FORMAT([First Day],"MM-YYYY"))
                    ),
                    "Chamados fechados" ,
                        CALCULATE(
                        COUNT('Controle de Ticket´s BI (2)'[ID Ticket]), 
                        ALL('Controle de Ticket´s BI (2)'[Data da Conclusão]),
                        FILTER('Controle de Ticket´s BI (2)', 'Controle de Ticket´s BI (2)'[Ticket Mtec.Tipo_Ticket__c] = [Tipo]),
                        FILTER('Controle de Ticket´s BI (2)', FORMAT('Controle de Ticket´s BI (2)'[Data da Conclusão], "MM-YYYY" ) = FORMAT([First Day],"MM-YYYY"))
                    )
                )

return dim_dados

Result:

[v-kkf-msft]

Hi @pafernandes ,

In my sample data, your formula returns the correct result. Could you please share your PBIX file?

Best Regards,
Winniz

[jennratten]

I can't read the script but it looks like the calculation is counting the tickets based on the created date instead of the closed date.