topic Re: Count Milestones Estimated in the Past in DAX Commands and Tips

Count Milestones Estimated in the Past

Gandarthvader — Fri, 03 Jan 2025 19:27:38 GMT

Hello, I'm not sure if this is doable the way I'm envisioning it. I have a card and I want to show a numerical count on it of milestones estimated in the past (including today) on projects. The table that I'm working with has unique project numbers and 9 milestones for each project number. The milestones each have a column for the date that the milestone is to be completed and then an "Actual" column where the milestone is set to either "Estimate" or "Actual". If it's set to "Actual" I would have the card ignore it, or if it's set to "Estimate" but the date is in the future, the count should ignore it. But, if it's set to "Estimate" and the date is today's date or in the past, I'd like to have it counted and added to the tally on the card.

Here are the individual milestone column names:

ProjectID, ProjectIDActual

Feasibility, FeasibilityActual

DueDiligence, DueDiligenceActual

PreCon, PreConActual

ConStrt, ConStrtActual

ConCompl, ConComplActual

Integration, IntegrationActual

PostCon, PostConActual

GrandOpen, GrandOpenActual

The first column for each milestone shows the date that is pulled for it and the one showing "Actual" just will say either "Estimate" or "Actual" in it. For instance, for the 'GrandOpen' column it will show a date and the 'GrandOpenActual' column will say whether it's Actual or Estimate.

So to recap; I'd like to see if there's a way to get a card to display a count of how many milestones are showing Estimate with today's date or a date in the past. Is this possible in one DAX function? Would it be set up as a measure and added to the card or best to make it a column on the table itself?

Thanks so much in advance for any help!

Re: Count Milestones Estimated in the Past

DataNinja777 — Sat, 04 Jan 2025 05:10:05 GMT

Hi Gandarthvader ,

Here’s a DAX measure to count milestones that are "Estimate" and have a date of today or in the past.

Estimated Milestones Count = VAR MilestonesToCheck = { ('Table'[Feasibility], 'Table'[FeasibilityActual]), ('Table'[DueDiligence], 'Table'[DueDiligenceActual]), ('Table'[PreCon], 'Table'[PreConActual]), ('Table'[ConStrt], 'Table'[ConStrtActual]), ('Table'[ConCompl], 'Table'[ConComplActual]), ('Table'[Integration], 'Table'[IntegrationActual]), ('Table'[PostCon], 'Table'[PostConActual]), ('Table'[GrandOpen], 'Table'[GrandOpenActual]) } RETURN COUNTROWS( FILTER( ADDCOLUMNS( MilestonesToCheck, "MilestoneDate", [Value1], "Status", [Value2] ), [Status] = "Estimate" && [MilestoneDate] <= TODAY() ) )

MilestonesToCheck is a virtual table containing all milestone dates and their corresponding "Actual" columns.

The FILTER function ensures only milestones marked as "Estimate" with a date today or earlier are counted.

The COUNTROWS function returns the number of qualifying milestones.

If You Want a Simpler Approach:

Power BI doesn't have a direct way to iterate through multiple columns dynamically. If simplicity is your goal, the best way is to unpivot your milestone columns in Power Query, making it easier to handle with a basic DAX formula. That would significantly simplify the DAX.

Best regards,

Re: Count Milestones Estimated in the Past

Poojara_D12 — Sat, 04 Jan 2025 06:45:31 GMT

Hi Gandarthvader

To count milestones with "Estimate" where the date is today or in the past:

Create a Measure: Write a DAX measure to filter rows for each milestone:
- Check if Actual = "Estimate".
- Check if the milestone date ≤ Today().

MilestonesPastOrTodayEstimate = COUNTROWS( FILTER( YourTableName, (YourTableName[FeasibilityActual] = "Estimate" && YourTableName[Feasibility] <= TODAY()) || (YourTableName[DueDiligenceActual] = "Estimate" && YourTableName[DueDiligence] <= TODAY()) || (YourTableName[PreConActual] = "Estimate" && YourTableName[PreCon] <= TODAY()) || (YourTableName[ConStrtActual] = "Estimate" && YourTableName[ConStrt] <= TODAY()) || (YourTableName[ConComplActual] = "Estimate" && YourTableName[ConCompl] <= TODAY()) || (YourTableName[IntegrationActual] = "Estimate" && YourTableName[Integration] <= TODAY()) || (YourTableName[PostConActual] = "Estimate" && YourTableName[PostCon] <= TODAY()) || (YourTableName[GrandOpenActual] = "Estimate" && YourTableName[GrandOpen] <= TODAY()) ) )

This measure evaluates all milestones, checks the conditions, and sums the results for the card.

Add to a Card: Drag this measure into a card visual to display the count dynamically.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

Re: Count Milestones Estimated in the Past

Anonymous — Mon, 06 Jan 2025 07:46:15 GMT

Hi Gandarthvader ,

You can create a calculated column as below in the table to get it, please find the details in the attachment.

Column = VAR _m1 = IF ( 'Table'[ProjectID] <= TODAY () && 'Table'[ProjectIDActual] = "Estimate", 1, 0 ) VAR _m2 = IF ( 'Table'[Feasibility] <= TODAY () && 'Table'[FeasibilityActual] = "Estimate", 1, 0 ) VAR _m3 = IF ( 'Table'[DueDiligence] <= TODAY () && 'Table'[DueDiligenceActual] = "Estimate", 1, 0 ) VAR _m4 = IF ( 'Table'[PreCon] <= TODAY () && 'Table'[PreConActual] = "Estimate", 1, 0 ) VAR _m5 = IF ( 'Table'[ConStrt] <= TODAY () && 'Table'[ConStrtActual] = "Estimate", 1, 0 ) VAR _m6 = IF ( 'Table'[ConCompl] <= TODAY () && 'Table'[ConComplActual] = "Estimate", 1, 0 ) VAR _m7 = IF ( 'Table'[Integration] <= TODAY () && 'Table'[IntegrationActual] = "Estimate", 1, 0 ) VAR _m8 = IF ( 'Table'[PostCon] <= TODAY () && 'Table'[PostConActual] = "Estimate", 1, 0 ) VAR _m9 = IF ( 'Table'[GrandOpen] <= TODAY () && 'Table'[GrandOpenActual] = "Estimate", 1, 0 ) RETURN _m1 + _m2 + _m3 + _m4 + _m5 + _m6 + _m7 + _m8 + _m9

Best Regards

Re: Count Milestones Estimated in the Past

Gandarthvader — Mon, 06 Jan 2025 14:35:56 GMT

Thank you so much! That seems to have worked. One of the measure options that was posted also worked to count the rows which I could have worked with, but this actually counted each past milestone so I think it will work best. Thanks again!