Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Hi @pafernandes ,
In my sample data, your formula returns the correct result. Could you please share your PBIX file?
Best Regards,
Winniz
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.