Cumulative Bar Graph based on date

[Anonymous]

Hi,

I need some assistance where I need to do the following:

I need to display a Bar Graph with the total people employed (date ) by Deparment. It needs to be cumulative and split by departmernt.

I have a date created and a date abolished (important as a position can be removed and another created)

So if it was abolished in 2016 and another was created in 2016 it will be a nil effect in the cumulative total.

I have the date created connected to my date table which is broken up by year and fiscal year.

[v-yulgu-msft]

Hi @Anonymous ,

Please illustrate your requirement with sample data and desired output.

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[Anonymous]

Sample Data

Hi,

I have tried to add as much as I can.

Can someone please assist.

[Anonymous]

Hi,

I have tried a few ways and sorry I have not added.

I have a Positions Table with a Date Approved field linked to the Date field in my Date Table.

Count FTE Measure = sum(Positions[FTE])

Cumulative FTE = 
                CALCULATE([Count FTE Measure],
                FILTER(ALLSELECTED('Date'[Date]),
                    'Date'[Date] <= MAX('Date'[Date])))I 

The date table has Dates, Months, Years, Fiscal Years etc

The issue I have is I need to have a cumulative count of all Active position in the year in a bar chart.

But I also have to report how many Date Abolished have been made in that year. The Date Abolished fireld is in the Positions Table

2016 / 2017  

Total = 300

2017/2018

Date Approved = 3

Date Abolished = 10

Total = 293

2018/2019

Date Approved = 7

Date Abolished = 3

Total = 297

I think the issue is that the Date in the Date table is connected to the Dtae Approved in the Positions Table but I also need to use the Date Abolished which is not linked to a Date Table

Any Ideas

[Anonymous]

Hi,

I have tried to attached some sample data.

Can anyone assist please.

https://docs.google.com/spreadsheets/d/1XyPTXzbveQtXiPTQZcSXN_4UWT8c69PE62MVnp0iw7w/edit?usp=sharing

[Ashish_Mathur]

Hi,

This is a start.  You may download my PBI file from here.  I have not computed the cumulative total, just the net additions per year.  Are the net addition figures correct?

[Anonymous]

Hi,

Thank you @Ashish_Mathur for this but it is not what I wanted

I have attached a file if it helps on what I'm trying to get to.

Power BI File  ===>> My Power BI File

[Ashish_Mathur]

Hi,

Sharing the PBI file does not help unless you tell me exactly what end result you want to see.  I am not interested in the graph.  Just show me in a Table the exact result you are expecting.

[Anonymous]

Thanks @Ashish_Mathur 

Ok, let me try again.

What I need is the total of FTE (sum) every year as at the end of the fiscal year. So if the position was approved in the F2012, then they are also to be included in the F2013 and so on. They should be included in all years until the position is abolished. Furthermore, there should be a net difference in the abolished so that is there was 3 positions added but there was 5 positions abolished then the net difference in that particular year drops by 2.

Same example as earlier listed again:

Not the actual numbers, I'm just giving an example.

2016 / 2017  

Total = 300

2017/2018

Date Approved = 3

Date Abolished = 10

Total = 293

2018/2019

Date Approved = 7

Date Abolished = 3

Total = 297

I hope this helps. I really appriciate any assistance as you can see from the report I have tried different ways.

I need to represent this in waterfall chart showing the differences every year and the total to be correct and the Fiscal years to be right as at the end of that fiscal year.

[Anonymous]

Can anyone please assist