Forum Discussion

ghouse_peer's avatar
ghouse_peer
Icon for Post Patron rankPost Patron
3 years ago

Dax to get Backlog count

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

TypeMonthStatusCounts
PBI1/1/2022Open126
PBI1/1/2022Closed125
PBI1/1/2022Backlog37
PBI2/1/2022Open468
PBI2/1/2022Closed459
PBI2/1/2022Backlog46
PBI3/1/2022Open535
PBI3/1/2022Closed534
PBI3/1/2022Backlog47
PBI4/1/2022Open49
PBI4/1/2022Closed49
PBI4/1/2022Backlog47
PBI5/1/2022Open126
PBI5/1/2022Closed126
PBI5/1/2022Backlog47
PBI6/1/2022Open197
PBI6/1/2022Closed197
PBI6/1/2022Backlog47
PBI7/1/2022Open1025
PBI7/1/2022Closed1023
PBI7/1/2022Backlog49
PBI8/1/2022Open753
PBI8/1/2022Closed748
PBI8/1/2022Backlog54
PBI9/1/2022Open77
PBI9/1/2022Closed61
PBI9/1/2022Backlog70
Pro1/1/2022Open5007
Pro1/1/2022Closed4997
Pro1/1/2022Backlog62
Pro2/1/2022Open2057
Pro2/1/2022Closed2053
Pro2/1/2022Backlog66
Pro3/1/2022Open4790
Pro3/1/2022Closed4782
Pro3/1/2022Backlog74
Pro4/1/2022Open4406
Pro4/1/2022Closed4395
Pro4/1/2022Backlog85
Pro5/1/2022Open3958
Pro5/1/2022Closed3923
Pro5/1/2022Backlog120
Pro6/1/2022Open4964
Pro6/1/2022Closed4914
Pro6/1/2022Backlog170
Pro7/1/2022Open5011
Pro7/1/2022Closed4930
Pro7/1/2022Backlog251
Pro8/1/2022Open2501
Pro8/1/2022Closed2455
Pro8/1/2022Backlog297
Pro9/1/2022Open831
Pro9/1/2022Closed719
Pro9/1/2022Backlog409
Premium1/1/2022Open13521
Premium1/1/2022Closed10056
Premium1/1/2022Backlog3749
Premium2/1/2022Open4390
Premium2/1/2022Closed3994
Premium2/1/2022Backlog4145
Premium3/1/2022Open2423
Premium3/1/2022Closed2262
Premium3/1/2022Backlog4306
Premium4/1/2022Open2076
Premium4/1/2022Closed1994
Premium4/1/2022Backlog4388
Premium5/1/2022Open4696
Premium5/1/2022Closed4298
Premium5/1/2022Backlog4786
Premium6/1/2022Open5553

 

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

Need Help
Show and Tell

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      ghouse_peer
      Icon for Post Patron rankPost Patron
      3 years ago

      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
    • ghouse_peer's avatar
      ghouse_peer
      Icon for Post Patron rankPost Patron
      3 years ago

      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.