cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gingerclaire_
Helper II
Helper II

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?

 

1F06513B-15EA-4B03-89C0-C3FC7A787662.jpeg

1 ACCEPTED SOLUTION
Jihwan_Kim
Community Champion
Community Champion

Hi, @Gingerclaire_ 

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

 

Picture5.png

 

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


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

9 REPLIES 9
v-janeyg-msft
Community Support
Community Support

Hi, @Gingerclaire_ 

 

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]
)

v-janeyg-msft_0-1619680846447.png

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.

Jihwan_Kim
Community Champion
Community Champion

Hi, @Gingerclaire_ 

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

 

Picture5.png

 

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


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

@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.

Hi, @Gingerclaire_ 

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.


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

@Jihwan_Kim I've added to the sample file you originally sent me - thanks! I hope it explain further what I'm trying to do.

 

https://1drv.ms/u/s!AhqzNpbJTZ04mjxY1_xG_o6Ubp8K?e=r25bgj

Hi, @Gingerclaire_ 

Thank you for sharing the data.

Please check the below picture and the pbix file's link down below whether I understood your question correctly.

I think you can use the below measures to calculate the percentage, for instance.

 

Picture2.png

https://www.dropbox.com/s/flfsc77ijnu27g5/SampleFile.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

 


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

@Jihwan_Kim Thank you - I've been able to work it out on the sample data set and get the correct calculations.

 

However, when I replicate the measures on my real data I can't get the 'parts finished' correct. It just comes up with the total in each row and not 1 as on the sample dataset. As a result I can't filter to the different milestone. Any thoughts why it might do that? 

tempsnip.png

Hi, @Gingerclaire_ 

Thank you for sharing.

If it is OK with you, please share your sample pbix file's link, then I can try to look into it.

I think it is because of the relationship between the two tables, but I need to check, if possible.

 

Thank you.


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.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors