Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Comunity,
I want to create a kind of time table with a matrix as in the following example in excel.
I have 6 milestones. Each number in The Excel corresponds to a milestone.
Example in Excel
Excel Formel
how do I get this in Power bi?
--> If the scheduled date (month and year) corresponds to an appointment in the matrix (month and year), add 1.
Matrix in Power BI
Solved! Go to Solution.
Hello @Lachsforelle12 ,
Taking into account the need you have, I have taken the following steps:
Milestones =
VAR end_date =
CALCULATE (
CONCATENATEX (
FILTER (
'Table';
'Table'[EndDate] <= MAX ( 'calendar'[Date] )
&& 'Table'[EndDate] <> BLANK ()
);
'Table'[Milestone];
UNICHAR ( 10 )
);
USERELATIONSHIP ( 'calendar'[Date]; 'Table'[EndDate] )
)
VAR start_date =
CONCATENATEX (
FILTER ( 'Table'; 'Table'[Date] <= MAX ( 'calendar'[Date] ) );
'Table'[Milestone];
UNICHAR ( 10 )
)
RETURN
SWITCH (
TRUE ();
start_date = BLANK ()
&& end_date = BLANK (); "0";
end_date = BLANK (); start_date;
end_date & UNICHAR ( 10 ) & start_date
)
Formatting =
SWITCH (
TRUE ();
EOMONTH ( MAX ( 'calendar'[Date] ); 0 ) = EOMONTH ( TODAY(); 0 )
&& [Milestones] = "0"; 2;
EOMONTH ( MAX ( 'calendar'[Date] ); 0 ) = EOMONTH ( TODAY(); 0 ); 1;
[Milestones] = "0"; 0
)
You must also create an inactive relationship between the calendar and the end date.
Now configure your condittional format according to this:
Check Attach File.
In terms of formatting things can be done in a different way depending on the way you need to do things, but you have the general idea.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
I assume you have a calendar table and a column with the current milesotne marked.
Created two measures:
Formatting = IF(SELECTEDVALUE('Table'[Current Milestone]) = "Current" ;1 ;IF(SELECTEDVALUE('calendar'[Month_Year]) = FORMAT(TODAY();"yyyymm");2;0))
Milestones = MAX(MAX('Table'[Milestone]);IF(SELECTEDVALUE('calendar'[Month_Year]) = FORMAT(TODAY();"yyyymm");1))
I have a colum with YYYYMM format on my calendar table now setup the matrix on the following way:
Setup the condittional formatting on the milestones on the following way:
Final result below and in attach PBIX file (october2020 version)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you. @MFelix
He needs other advice.
I have 6 milestones for each project (1.A, 2.B, 3.C, 4.D, 5.E, 6.F) and a start date and an end date.
How can I apply your proposal to my data?
My calendar table is in the main table. This was created with the "Calendaauto" function.
this is what it should look like (both tables are connected by an index)
Hi @Anonymous ,
There area couple of things that I'm missing:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêshttps://www.youtube.com/watch?v=SO4mk1H94OA
--> That was my inspiration to build a Gantt in a year or a month's view
--> have created a new date table with unique values (Period 2019 to 2030)
--> Yes, I want to see the start and end date if possible.
--> milestones can overlap... is this generally not possible?
--> it is not important to know what the current MEilenstein is.
It is only important to see as a kind of summary of when the milestone is planned (start date).
It would also be nice to see when a milestone has been reached. It's supposed to be some kind of Gantt chart.
In addition, it would be nice to see what the current month is, as described at the beginning.
Hello @Lachsforelle12 ,
Taking into account the need you have, I have taken the following steps:
Milestones =
VAR end_date =
CALCULATE (
CONCATENATEX (
FILTER (
'Table';
'Table'[EndDate] <= MAX ( 'calendar'[Date] )
&& 'Table'[EndDate] <> BLANK ()
);
'Table'[Milestone];
UNICHAR ( 10 )
);
USERELATIONSHIP ( 'calendar'[Date]; 'Table'[EndDate] )
)
VAR start_date =
CONCATENATEX (
FILTER ( 'Table'; 'Table'[Date] <= MAX ( 'calendar'[Date] ) );
'Table'[Milestone];
UNICHAR ( 10 )
)
RETURN
SWITCH (
TRUE ();
start_date = BLANK ()
&& end_date = BLANK (); "0";
end_date = BLANK (); start_date;
end_date & UNICHAR ( 10 ) & start_date
)
Formatting =
SWITCH (
TRUE ();
EOMONTH ( MAX ( 'calendar'[Date] ); 0 ) = EOMONTH ( TODAY(); 0 )
&& [Milestones] = "0"; 2;
EOMONTH ( MAX ( 'calendar'[Date] ); 0 ) = EOMONTH ( TODAY(); 0 ); 1;
[Milestones] = "0"; 0
)
You must also create an inactive relationship between the calendar and the end date.
Now configure your condittional format according to this:
Check Attach File.
In terms of formatting things can be done in a different way depending on the way you need to do things, but you have the general idea.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |