Forum Discussion

suneelkethagani's avatar
suneelkethagani
Frequent Visitor
3 years ago

Need DAX Code

Hi Guys,

I hope you all are doing well.

 

I have a question that is in my data I have Project IDs and Project Statuses along with Dates.

If the Project is changed from Status 1 to Status 2 then need to calculate that specific project is there in Status 1 in how many months. 

 

For example, "Project 1" starts with "Status 1" on August 10, 2022 and changed to "Status 2" on December 5, 2022. In that case, I need to present that Project "Project 1" under "Status 1" in August, September, October, November and December 2022. Like that I need to present all the Projects in all the statuses.

 

Each Status in separate "Line and Stacked Column Chart". 

On X-Axis, Month

On Y-Axis, Avg no. of Days spent

On Line, No. of Projects

 

Could you please help me?

 

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi suneelkethagani ,

     

    From your example, Project ID: Project2022-01, Project2022-02... Do they represent the same Project or different ones? Also, some rows have only one status with a date, what should I do in this case?

    Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

    Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards,

    Jianbo Li

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

    • suneelkethagani's avatar
      suneelkethagani
      Frequent Visitor
      3 years ago

      Hi  v-jianboli-msft 

       

      Project ID: Project2022-01, Project2022-02... are the different project ids. 01, 02, 03,... represents ids. 
      Some projects starts with Status1, some projects starts with Status2,.... but not starts with Status5 and Status6.

      https://drive.google.com/file/d/1m-2DX4knZdqESCCUT6KlNyht4TcQQDJf/view?usp=share_link 

      Line and Stacked Column Chart:

      If the "Project 1"starts with Intake on January 1, 2022 and changed to Feasibility on March 15, 2022. Then 

      We need to show that project in January, February and March 2022 as count in line

      and

      In January 31 days, February 28 days and March 15 days need to show their averages in bars.

      X-axis is Months

       

      Thank you in advance