cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBi_Batman
Helper V
Helper V

VSTS - Closed by month (or set period) count

hi all

 

trying to create a simple bar graph that counts the total amount of items closed per month

 

using Custom WITs in VSTS doesnt allow me to obtain a 'Closed Date'. Only out of the box WITs have closed dates filled in

 

therefore i have been using the Date field and grouping by Month.

my current output is count of closed items per WIT total per month. The differences per month in terms of what was closed is correct.

 

what i am trying to achieve is just the count of closed items in that month, rather than cumaltive total

 

any help would be appreciated

1 ACCEPTED SOLUTION

Stacked Column chart

 

Capture1.JPG

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

11 REPLIES 11
CheenuSing
Community Champion
Community Champion

Hi @PowerBi_Batman

 

The information provided is not sufficient to suggest a solution.

 

Please share data/data model and pbix file on google drive / onedrive and paste the link here.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!


@CheenuSing wrote:

Hi @PowerBi_Batman

 

The information provided is not sufficient to suggest a solution.

 

Please share data/data model and pbix file on google drive / onedrive and paste the link here.

 

 

Cheers

 

CheenuSing


 

Hi

 

i probably wont be able to provide a file as it has the real data

 

the data is obtained using the In built VSTS source in powerbi desktop

 

attached is a screenshot of the current report

it shows the closed items cumulation per month and whereas i would rather show the actual total closed in that month/period

 

Screenshot.PNG

 

the month on month differences are correct when i compare them with a manual count or through a VSTS query

 

though i am assuming i will need to create a custom column or table to calculate month on month differences?

@PowerBi_Batman,

Please share dummy data of your table and post expected result so that we can provide you appropriate DAX.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ItemClosed Date
Yellow3/10/2018
Orange4/10/2018
Yellow4/10/2018
Orange5/10/2018
Yellow28/09/2018
Blue15/09/2018
Red2/10/2018

 

Expected result would be a stacked graph or bar chart that would show the count of closed per month

 

September

Yellow - 1

Blue - 1

 

October

Yellow - 2

Orange - 2

Red - 1

 

Currently it will show Yellow as 3 in October as it is September + October etc

 

Hi @PowerBi_Batman

 

 

With the data you provided, I did

 

1. Put the Closed Date --> Year & Month as axis

2. Put the Legend as --> Item

3. Values as --> Count of item

 

And the output I got

 

Capture1.JPG

 

 

If this solves your issue please accpet it as a solution and give KUDOS.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

no it doesnt seem to work

 

i must be doing something wrong or the date data i have has issues

@CheenuSing

 

can you change your graph to a stacked column chart

currently yours is a 100% stacked column chart

 

thanks

 

 

Stacked Column chart

 

Capture1.JPG

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!


@CheenuSing wrote:

Stacked Column chart

 

Capture1.JPG

 

Cheers

 

CheenuSing



ill have to accept that is the solution

 

although ive tried numerous times on my data and it will not work, so i will need to dig deeper on the issue, as i wont be able to provide you with real data or a copy of the pbix file

 

 

ok the issue lies with the out of the box Closed Date field in VSTS

 

if you using custom work items, you arent presented with closed date values in those items

 

therefore you are forced to use the Date field provided when you load up the VSTS data and it will not be possible to provide a correct count when using that field. unless i create some custom columns calcuting current month figure and minus previous month figure

 

ive got a thread open with microsoft dealing with this closed date issue

 

 

ill have to accept that is the solution

 

although ive tried numerous times on my data and it will not work, so i will need to dig deeper on the issue, as i wont be able to provide you with real data or a copy of the pbix file

 

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!