Dax to get Backlog count

[ghouse_peer]

Hi team,

 I have a requirement to show the backlog count in Line y axis in Line and clustered column chart.

My data is in excel file

Type	Month	Status	Counts
PBI	1/1/2022	Open	126
PBI	1/1/2022	Closed	125
PBI	1/1/2022	Backlog	37
PBI	2/1/2022	Open	468
PBI	2/1/2022	Closed	459
PBI	2/1/2022	Backlog	46
PBI	3/1/2022	Open	535
PBI	3/1/2022	Closed	534
PBI	3/1/2022	Backlog	47
PBI	4/1/2022	Open	49
PBI	4/1/2022	Closed	49
PBI	4/1/2022	Backlog	47
PBI	5/1/2022	Open	126
PBI	5/1/2022	Closed	126
PBI	5/1/2022	Backlog	47
PBI	6/1/2022	Open	197
PBI	6/1/2022	Closed	197
PBI	6/1/2022	Backlog	47
PBI	7/1/2022	Open	1025
PBI	7/1/2022	Closed	1023
PBI	7/1/2022	Backlog	49
PBI	8/1/2022	Open	753
PBI	8/1/2022	Closed	748
PBI	8/1/2022	Backlog	54
PBI	9/1/2022	Open	77
PBI	9/1/2022	Closed	61
PBI	9/1/2022	Backlog	70
Pro	1/1/2022	Open	5007
Pro	1/1/2022	Closed	4997
Pro	1/1/2022	Backlog	62
Pro	2/1/2022	Open	2057
Pro	2/1/2022	Closed	2053
Pro	2/1/2022	Backlog	66
Pro	3/1/2022	Open	4790
Pro	3/1/2022	Closed	4782
Pro	3/1/2022	Backlog	74
Pro	4/1/2022	Open	4406
Pro	4/1/2022	Closed	4395
Pro	4/1/2022	Backlog	85
Pro	5/1/2022	Open	3958
Pro	5/1/2022	Closed	3923
Pro	5/1/2022	Backlog	120
Pro	6/1/2022	Open	4964
Pro	6/1/2022	Closed	4914
Pro	6/1/2022	Backlog	170
Pro	7/1/2022	Open	5011
Pro	7/1/2022	Closed	4930
Pro	7/1/2022	Backlog	251
Pro	8/1/2022	Open	2501
Pro	8/1/2022	Closed	2455
Pro	8/1/2022	Backlog	297
Pro	9/1/2022	Open	831
Pro	9/1/2022	Closed	719
Pro	9/1/2022	Backlog	409
Premium	1/1/2022	Open	13521
Premium	1/1/2022	Closed	10056
Premium	1/1/2022	Backlog	3749
Premium	2/1/2022	Open	4390
Premium	2/1/2022	Closed	3994
Premium	2/1/2022	Backlog	4145
Premium	3/1/2022	Open	2423
Premium	3/1/2022	Closed	2262
Premium	3/1/2022	Backlog	4306
Premium	4/1/2022	Open	2076
Premium	4/1/2022	Closed	1994
Premium	4/1/2022	Backlog	4388
Premium	5/1/2022	Open	4696
Premium	5/1/2022	Closed	4298
Premium	5/1/2022	Backlog	4786
Premium	6/1/2022	Open	5553

Requirement : I want only backlog count values w.r.t Months.

                    Ex: For Jan: PBI Backlog is 37, Pro Backlog is 62, Premium Backlog is 3749

when i take this into Line Y axis field i need to get this values as per every month backlog values.

Kindly help me with the dax for this.

Thank you

[v-xiaotang]

Hi @ghouse_peer 

Thanks for reaching out to us.

you can try this measure

Measure = SUMX(FILTER(ALL('Table'),YEAR('Table'[Month])=YEAR(MIN('Table'[Month])) && MONTH('Table'[Month])= MONTH(MIN('Table'[Month])) && 'Table'[Status]="Backlog"),[Counts])

it calculates the total of Backlog in each month.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly

[ghouse_peer]

@v-xiaotang and the other thing is it is showing deifferent values, For ex when i take 'Type' in Slicer and created measure in Line Y axis. When i select PBI in Slicer it should show Backlog : 36 for Jan and 46for Feb  (shown in data)  so on in Line. Please check &help.

[ghouse_peer]

Hi @v-xiaotang thanks for the help,

i tried this measure but getting error at 

'Table'[Status]= "Backlog"),[Counts]), error: Cannot find name "Backlog", but in my data it is same as i mentioned. Kindly help