Solved: Re: Matrix

Jyaul1122 · ‎10-08-2024

Hello Friends,

I have Stage table(Project,Stage_Short,Finish date) which is related with calendar table.

Stage Table data:

Project	Stage_Short	Finish
Project P1	S1	8-Dec-22
Project P1	S2	28-Nov-24
Project P1	S2	7-Apr-25
Project P1	S3	4-Nov-25
Project P1	S6	19-Apr-26
Project P1	S4	21-Apr-26
Project P1	S5	22-Sep-26
Project P1	S6	9-Nov-26
Project P1	S6	18-Jul-27
Project P1	S6	20-Aug-28
Project P1	S7	1-Apr-29
Project P1	S7	1-Apr-29
Project P1	S7	28-Jun-29
Project P1	S7	25-Dec-29

and I displayed in matrix with Columns Year and Month:

actually I want to display in matrix like: S1 start from 1 2022 and end to 12 i.e. S1 value in each month

same for S2: start from 1 2023 and end to 4 2025

S3: start from 5 2025 and end to 11 2025

S4: start from 12 2025 and end to 4 2026

S5: start from 5 2026 and end to 9 2026 and so on

End to always max date, but start is after next Stage.

how can i write measures using dax so that i will achieve ?

Anonymous · ‎10-09-2024

Hi @Jyaul1122 ,
This seems to work if you use a matrix for the representation, but if you need an easier way, you can try other visualization like Gantt, just need original table

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

Anonymous · ‎10-08-2024

Hi @Jyaul1122 ,
You can try the following steps
Create column in Stage Table

Start_time = 
VAR CurrentStage = 'Stage Table'[Stage_Short]
VAR CurrentFinish = 'Stage Table'[Finish]
VAR PreviousFinish = 
    CALCULATE(
        MAX('Stage Table'[Finish]),
        FILTER(
            'Stage Table',
            'Stage Table'[Project] = EARLIER('Stage Table'[Project]) &&
            'Stage Table'[Stage_Short] < CurrentStage
        )
    )
RETURN
IF(
    ISBLANK(PreviousFinish),
    DATE(YEAR(CurrentFinish), 1, 1),
    EOMONTH(PreviousFinish, 0) + 1
)

End_time = 
VAR _maxdate = 
CALCULATE(
    MAX('Stage Table'[Finish]),
    ALLEXCEPT(
        'Stage Table',
        'Stage Table'[Stage_Short]
    )
)
RETURN
EOMONTH(_maxdate,0)

Create Clendar Table

Calendar Table = 
CALENDAR(
    DATE(YEAR(MIN('Stage Table'[Finish])),1,1),
    DATE(YEAR(MAX('Stage Table'[Finish])),12,31)
)

Create columns in calendar table

Year = YEAR('Calendar Table'[Date])

Month Number = 'Calendar Table'[Date].[MonthNo]

Stage = 
SWITCH(
    TRUE(),
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S1")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S1")),
    "S1",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S2")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S2")),
    "S2",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S3")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S3")),
    "S3",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S4")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S4")),
    "S4",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S5")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S5")),
    "S5",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S6")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S6")),
    "S6",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S7")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Stage_Short] = "S7")),
    "S7"
)

Project = VALUES('Stage Table'[Project])

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Jyaul1122 · ‎10-09-2024

Dear Helper,

I have multiple number of project, how you will do

Project = VALUES('Stage Table'[Project])

in calendar table,

Anonymous · ‎10-09-2024

Hi @Jyaul1122 ,
If you have other projects, first you need to create a column that can be recognized separately

Project_Stage = CONCATENATE('Stage Table'[Project],'Stage Table'[Stage_Short])

for start_time and end_time you need to change it like this

Start_time = 
VAR CurrentStage = 'Stage Table'[Stage_Short]
VAR CurrentFinish = 'Stage Table'[Finish]
VAR PreviousFinish = 
    CALCULATE(
        MAX('Stage Table'[Finish]),
        FILTER(
            ALLEXCEPT(
                'Stage Table',
                'Stage Table'[Project]
            ),
            'Stage Table'[Project] = EARLIER('Stage Table'[Project]) &&
            'Stage Table'[Stage_Short] < CurrentStage
        )
    )
RETURN
IF(
    ISBLANK(PreviousFinish),
    DATE(YEAR(CurrentFinish), 1, 1),
    EOMONTH(PreviousFinish, 0) + 1
)

End_time = 
VAR _maxdate = 
CALCULATE(
    MAX('Stage Table'[Finish]),
    ALLEXCEPT(
        'Stage Table',
        'Stage Table'[Stage_Short],
        'Stage Table'[Project]
    )
)
RETURN
EOMONTH(_maxdate,0)

For the calendar table you need to create two columns

Project_Stage = 
SWITCH(
    TRUE(),
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S1")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S1")),
    "Project P1S1",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S2")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S2")),
    "Project P1S2",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S3")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S3")),
    "Project P1S3",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S4")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S4")),
    "Project P1S4",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S5")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S5")),
    "Project P1S5",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S6")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S6")),
    "Project P1S6",
    'Calendar Table'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S7")) &&
    'Calendar Table'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P1S7")),
    "Project P1S7"
)

Stage = RIGHT('Calendar Table'[Project_Stage],2)

The data specified here is from project1, and you need to create another calendar table for project2. Same steps as above

Project_Stage = 
SWITCH(
    TRUE(),
    'Calendar Table2'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S1")) &&
    'Calendar Table2'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S1")),
    "Project P2S1",
    'Calendar Table2'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S2")) &&
    'Calendar Table2'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S2")),
    "Project P2S2",
    'Calendar Table2'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S3")) &&
    'Calendar Table2'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S3")),
    "Project P2S3",
    'Calendar Table2'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S4")) &&
    'Calendar Table2'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S4")),
    "Project P2S4",
    'Calendar Table2'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S5")) &&
    'Calendar Table2'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S5")),
    "Project P2S5",
    'Calendar Table2'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S6")) &&
    'Calendar Table2'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S6")),
    "Project P2S6",
    'Calendar Table2'[Date] >= CALCULATE(MAX('Stage Table'[Start_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S7")) &&
    'Calendar Table2'[Date] <= CALCULATE(MAX('Stage Table'[End_time]),FILTER('Stage Table','Stage Table'[Project_Stage] = "Project P2S7")),
    "Project P2S7"
)

Stage = RIGHT('Calendar Table2'[Project_Stage],2)

Use the union function to join these two tables, provided that the calendar table has the same number of columns and that the absolute positions of these columns correspond to each other.

Table = UNION('Calendar Table','Calendar Table2')

Create a new column

Project = LEFT('Table'[Project_Stage],10)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Jyaul1122 · ‎10-09-2024

Hi ,

Thanks for your reply but I have 100 of project means i have to create 100 dates table ?

Anonymous · ‎10-09-2024

Hi @Jyaul1122 ,
This seems to work if you use a matrix for the representation, but if you need an easier way, you can try other visualization like Gantt, just need original table

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Matrix

Helpful resources

New to Fabric Survey

Power BI DataViz World Championships - June 2026

Join our Fabric User Panel

Power BI Community Update - March 2026

How to Get Your Question Answered Quickly

Get Fabric certified for FREE!

Matrix

Helpful resources

New to Fabric Survey

Power BI DataViz World Championships - June 2026

Join our Fabric User Panel

Power BI Community Update - March 2026

How to Get Your Question Answered Quickly