Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Project Milestones Filter

I want to create a measure to show which milestone my project is on through Power BI. I've created a table which has the milestone, start date and end date. It will refresh once a week to move projects onto the next milestone.

 

What measure do I use to say if date is in-between column B & column C then return column A?

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

     

     

    Milestone Flag =
    CALCULATE (
    SELECTEDVALUE ( Milestone[Milestone] ),
    FILTER (
    Milestone,
    Milestone[Start] <= MAX ( 'Calendar'[Date] )
    && Milestone[End] >= MIN ( 'Calendar'[Date] )
    )
    )

     

     

    https://www.dropbox.com/s/7z7qg9wmicykzh3/Gingerclaire.pbix?dl=0 

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

9 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

     

     

    Milestone Flag =
    CALCULATE (
    SELECTEDVALUE ( Milestone[Milestone] ),
    FILTER (
    Milestone,
    Milestone[Start] <= MAX ( 'Calendar'[Date] )
    && Milestone[End] >= MIN ( 'Calendar'[Date] )
    )
    )

     

     

    https://www.dropbox.com/s/7z7qg9wmicykzh3/Gingerclaire.pbix?dl=0 

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Jihwan_Kim That's exactly what I was trying to do - thank you! 

      I have another measure in my dashboard which calculates what % of parts are finished. Can I use this milestone flag to show what % of parts are finished for milestone two for example?


      Tried a few different filters and can't figure it out.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, Anonymous 

        Thank you for your feedback.

        I am not quite sure if I understood your question correctly, but I think the measure is only showing the text whether it is Zero or One or Two.

        If you want to calculate the percentage, I think you need to write a measure using countrows or similar to it to calculate how many parts are finished among all parts.

        If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

        Thank you.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    You can easliy create a calendar table and use the date as slicer, and create a measure to show the result.

    Like this:

    Table 2 = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))
    Measure =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        [Start] <= SELECTEDVALUE ( 'Table 2'[Date] )
            && [End] >= SELECTEDVALUE ( 'Table 2'[Date] )
    ),
    [Milestone]
)

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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