Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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 Ex...
Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    6 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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

https://www.youtube.com/watch?v=SO4mk1H94OA

--> That was my inspiration to build a Gantt in a year or a month's view

  • Why does the calendar table have repeated values? (this table ttype should only have unique values)

--> have created a new date table with unique values (Period 2019 to 2030)

Bild5.png

  • Do you want to display the start and end date of milestones in the visualization?

--> Yes, I want to see the start and end date if possible.

  • Assuming you don't have miles overlaid

--> milestones can overlap... is this generally not possible?

  • If you only want one, which one is presented star or finished?
  • How do you know it's the current one?
    • When does the milestone end do you get the same date for the beginning and end?
  • How is your relationship between the calendar date by date?

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

@MFelix

Bild6.png

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 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.