evolution in column

[hyugo_v]

Hello everybody,

i'm currently working with power bi on a mission currently but i'm facing a problem which i still can't resolve, i put in attachment the illustration on what i will talk now.

I got a data source which contains lots of parameters but i reduced it on the attachment in order to be more understandable.

For each line , i have a projet, a start and a end of that projet, i got an ETC which basically means workload (hrs) necessary in order to complete that project, finally i got "t' which is use for speading the workload for each week, as we can see we couldn't use all the workload in the first week that's why we have the "41.79" hrs remaining for the second week.

I think you get the idea, i want a graph who represent the workload remaining through the weeks

The problem is that the evolution is on column not on lines, it means i have to choose the date or the value of that date, how can i do to use all the data in order to have the vizualisation i'm looking for ?

Sorry for my english if it's not really understandable, i'll try to more clear if needed,

Here is the screenshot from an excel tab i create(the original datasource is excel too). 

[v-xicai]

Hi @hyugo_v ,

You can create a calculated table named DateKey .

DateKey = CALENDARAUTO()

Then, create measure like DAX below.

measure1= CALCULATE(SUM('Table'[Every day workload]),FILTER(ALLSELECTED('Table'),'Table'[NET START]<=MAX('DateKey'[Date])&&'Table'[FINISH DATE]>=MIN('DateKey'[Date])))

Finally, try to choose Clustered column chart visual to show the result, drag the DateKey[Date] on Axis and drag the measure1 on Value .

If I misunderstand it, could you please describe how to get the data of "t", you can also share your sample data or desired output screenshots for further analysis, or upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

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

[hyugo_v]

First of all, thank you @v-xicai for responding to my message quickly , 

you explanation was really clear, i tried to do as you said  but when i want to select the table of "net start " and " finish date " it doesnt show up, do you have an idea ? 

I put the screenshot of the "t" value, for each week we multply hours per day * number of working days in a week, and we do the same operation for each week until we use all of the ETC.

I hope it's enough visible but the red line "Weekly workload" is the sum of all the ETC of each project for each week,

the result of that is shown in the graph under, 

i'll continue to look after it, if you need further information,

Best regards, 

Hugo

Here is the formulate of the value of "t"

[v-xicai]

Hi @hyugo_v ,

For the FILTER condition, you can create like DAX below, then you can select the table of "net start " and " finish date ".

FILTER(ALLSELECTED('Table_Wavegraph'),'Table_Wavegraph'[NET START]<=MAX('DateKey'[Date])&&'Table_Wavegraph'[FINISH DATE]>=MIN('DateKey'[Date]))

Could you please clarify or show your desired output, such as in which visual ,which field on X axis, which field on Y axis?

Best Regards,

Amy

[hyugo_v]

Hi @v-xicai ,

the formula you said is right ! i didn't type it correctly, i have indeed a visualisation which is closer to what i want,

to answer the question you asked, i want in the X axis the evolution in time (1/Apr/19, 8/Apr/19 etc...) and in the Y axis the sum of ETC of the projects as we see just above the dates in yellow (3123, 2835 etc...) 

Which normally leads to a visualisation like as follow, your formula looks really correct but i don't find the good value yet, however it's getting closer 🙂 sum of ETC (Y axis) through the time (X axis)

[hyugo_v]

@v-xicai, 

i got a visualisation like this, i'll try to find why i got different values, until now thank you for your help 🙂 

[v-xicai · Accepted solution]

Hi  @hyugo_v ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy