Need help creating a Matrix visual to display duration between dates

[YogeshS]

I am using a Matrix table where in row i added vehicle number and in Column I added date. I want to show 1 from the date of Breakdown till the Ready date. Help me to slove this.

For Example:

Vehicle No	Breakdown Date	Ready Date
12345	02-01-2024	06-01-2024
67898	04-01-2024	08-01-2024
12352	01-01-2024	02-01-2024

Result needed to be:

Vehicle No	01-01-2024	02-01-2024	03-01-2024	04-01-2024	05-01-2024	06-01-2024	07-01-2024	08-01-2024	09-01-2024	10-01-2024
12345		1	1	1	1	1
67898				1	1	1	1	1
12352	1	1

[YogeshS]

Thank You!!
@v-yilong-msft , @Ashish_Mathur 
It worked.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[v-yilong-msft]

Hi @YogeshS,

I’d like to acknowledge the valuable input provided by the @Uzi2019. His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. In my investigation, I took the following steps:

I create two tables as you mentioned.

Then I create a Table 2.

Table 2 =
ADDCOLUMNS (
    CROSSJOIN ( 'Date (2)', 'Table' ),
    "IF",
        IF (
            'Date (2)'[Date] >= 'Table'[Breakdown Date]
                && 'Date (2)'[Date] <= 'Table'[Ready Date],
            1,
            0
        )
)

Finally you will get what you want.

If you want to let the 0 become the blank, I think you can do some changes.

Table 2 =
ADDCOLUMNS (
    CROSSJOIN ( 'Date (2)', 'Table' ),
    "IF",
        IF (
            'Date (2)'[Date] >= 'Table'[Breakdown Date]
                && 'Date (2)'[Date] <= 'Table'[Ready Date],
            1,
            BLANK ()
        )
)

Best Regards

Yilong Zhou

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

[Uzi2019]

Hi @YogeshS 

what is 1 represent?? is it count of Nobreakdown or first date like date 1 of every month?

[YogeshS]

Hi...
We can use any representation, even text like 'BD', to indicate that a vehicle was at breakdown on a certain date. I used '1' as a placeholder, but the objective is to display the dates when each vehicle experienced a breakdown and the placeholder should be filled on the following dates until the ready date.