Hello Friends, I have Stage table(Project,Stage_Short,Finish date) which is related with calendar table.Stage Table data:ProjectStage_ShortFinishProject P1S18-Dec-22Project P1S228-Nov-24Project P1S27-Apr-25Project P1S34-Nov-25Project P1S619-Apr-26Project P1S421-Apr-26Project P1S522-Sep-26Project P1S69-Nov-26Project P1S618-Jul-27Project P1S620-Aug-28Project P1S71-Apr-29Project P1S71-Apr-29Project P1S728-Jun-29Project P1S725-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 2025S3: start from 5 2025 and end to 11 2025S4: start from 12 2025 and end to 4 2026S5: 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 ?

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 | Microsoft Fabric Community

5 Replies

Anonymous

Not applicable

1 year ago

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

Matrix.pbix101 KB

Jyaul1122

Helper III

1 year ago

Dear Helper,

I have multiple number of project, how you will do

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

in calendar table,

Anonymous

Not applicable

1 year ago

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

Matrix.pbix153 KB

Forum Discussion

Matrix

5 Replies

Related content

Matrix table

sorting matrix

Customize subtotal in matrix

Matrix formatting

Matrix Row Conditional Formatting

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