Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Matrix Timetable - Power BI solution

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?

  • How can I highlight the current month in the Power BI Matrix?
  • How can I enter my milestones in the matrix as in Excel?

--> If the scheduled date (month and year) corresponds to an appointment in the matrix (month and year), add 1.

 

 

Matrix in Power BI

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    5 years ago

    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:

    MFelix_0-1603368697121.png

    MFelix_1-1603368724437.png

    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.

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi 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:

    • Rows: Project
    • Columns: Months
    • Values: Milestones

    Setup the condittional formatting on the milestones on the following way:

     

     

     

    Final result below and in attach PBIX file (october2020 version)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank 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.

      Bild4.png

      this is what it should look like (both tables are connected by an index)

      Bild3.png

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi Anonymous ,

         

        There area couple of things that I'm missing:

        • Why is your calendar table with repeated values? (this ttype of tables should only have unique values)
        • Do you want to show the start and end date of the milestones on the visualization?
          • Assuming you don't have milesotnes overlapsed
          • If you only want one what is the one to be presented star or finish?
        • How do you know it's the current one?
          • When milestone are finish they stay with the same date for beggining and ending?
        • Does your relationship between the calendar date how is it done by date?