Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User
Super User

Hi, @Anonymous 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

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

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

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
Super User
Super User

Hi, @Anonymous 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

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

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


Go to My LinkedIn Page


Anonymous
Not applicable

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

 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

@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
Not applicable

tempsnip.png

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.

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


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.