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

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?
Anonymous's avatar
Anonymous
Not applicable
5 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
    Super User
    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.