Forum Discussion

hyugo_v's avatar
hyugo_v
Helper I
7 years ago
Solved

evolution in column

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.   ...
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    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

v-xicai's avatar
v-xicai
Community Support
7 years ago

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's avatar
    hyugo_v
    Helper I
    7 years ago

    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's avatar
      v-xicai
      Community Support
      7 years ago

      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's avatar
        hyugo_v
        Helper I
        7 years ago

        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)