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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Help with Matrix table for Dates

Hi

 

Below is the sample table that I am using. I want to populate a roll up data using Matrix table or table for the no of projects from sharepoint list

Project NameTask NamePlanned StartPlanned FinishActual StartActual FinishActivity status
ProjectATask11-Mar-222-Mar-221-Mar-222-Mar-22Completed
ProjectATask22-Mar-223-Mar-222-Mar-223-Mar-22Completed
ProjectATask33-Mar-224-Mar-223-Mar-224-Mar-22Completed
ProjectATask44-Mar-225-Mar-224-Mar-225-Mar-22Completed
ProjectATask55-Mar-226-Mar-225-Mar-226-Mar-22Completed
ProjectATask66-Mar-2211-Mar-226-Mar-2211-Mar-22Completed
ProjectATask77-Mar-2220-Mar-227-Mar-22 In Progress
ProjectATask88-Mar-2225-Mar-22 8-Mar-22  In Progress

 

 

I want to show roll up data as below in Power BI . Actual finish should fill up only when all activities of ProjectA gets completed,till then it should empty..Is there any possible way to accomplish this. Thanks

Project NamePlanned StartPlanned FinishActual StartActual Finish
ProjectA1-Mar-2225-Mar-221-Mar-22 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Anonymous 

Looks like 4 measures

jdbuchanan71_0-1647268453550.png

Min Planned Start = MIN ('Table'[Planned Start] )
Max Planned Finish = MAX ('Table'[Planned Finish] )
Min Actual Start = MIN ('Table'[Actual Start] )
Max Actual Finish = 
VAR _Blanks = CALCULATE ( COUNTROWS ('Table'), ISBLANK ( 'Table'[Actual Finish] ) )
RETURN 
IF ( _Blanks = 0, MAX ('Table'[Actual Finish] ), BLANK() )

You can change the names of the measures in the table to remove the Min and Max if you want.

 

 

View solution in original post

3 REPLIES 3
jdbuchanan71
Super User
Super User

@Anonymous 

Looks like 4 measures

jdbuchanan71_0-1647268453550.png

Min Planned Start = MIN ('Table'[Planned Start] )
Max Planned Finish = MAX ('Table'[Planned Finish] )
Min Actual Start = MIN ('Table'[Actual Start] )
Max Actual Finish = 
VAR _Blanks = CALCULATE ( COUNTROWS ('Table'), ISBLANK ( 'Table'[Actual Finish] ) )
RETURN 
IF ( _Blanks = 0, MAX ('Table'[Actual Finish] ), BLANK() )

You can change the names of the measures in the table to remove the Min and Max if you want.

 

 

amitchandak
Super User
Super User

@Anonymous , A new column

new column =

var _cnt = countx(filter(Table, [Project Name] = Earlier([Project Name]) && isblank([Actual Finish]) ), [Project Name] )

return

if(isblank(_cnt), [Actual Finish], blank()) 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Its showing error .Syntax error.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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