Project Milestones Filter

[Anonymous]

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?

 

[v-janeyg-msft]

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.

[Jihwan_Kim · Accepted solution]

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]

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

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.

[Anonymous]

@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

[Jihwan_Kim]

Hi, @Anonymous 

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.

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

[Anonymous]

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

[Anonymous]

 

[Jihwan_Kim]

Hi, @Anonymous 

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.