Matrix with dynamic rows?

Ashish_Mathur , would you be able to help on this?

Hi,

I do not understand your question.  Vehicles DTR-466,467,468 and 469 have no record at all in the second table.  Therefore, when one selects the date as 30/1/2020, why should these appear in the final result?

You may try the following solution. It is not an optimum solution, but a simple one to start with.

Step 1:

Add a calendar table to your data model. Select "New Table" and use the following DAX code.

Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,2,29))

Change the date ranges as per your requirement. 

Step 2:

Add another calculated table to your data model. Select "New Table" and use the following DAX code.

VehicleCalendar = CROSSJOIN(Vehicles,'Calendar')

Step 3:

Add two calculated columns to the "VehicleCalendar" table that you have added in Step 2.

Appointed Department =
CONCATENATEX (
    FILTER (
        'Appointed Department',
        AND (
            AND (
                'Appointed Department'[From] <= VehicleCalendar[Date],
                'Appointed Department'[TillDate] >= VehicleCalendar[Date]
            ),
            'Appointed Department'[Vehicle] = VehicleCalendar[Vehicle]
        )
    ),
    'Appointed Department'[Appointed Department]
)

Relevant Department =
IF (
    ISBLANK ( VehicleCalendar[Appointed Department] ),
    VehicleCalendar[Standard Department],
    VehicleCalendar[Appointed Department]
)

Step 3a: Add another calculated column to the "Appointed Department" table.

TillDate = IF(ISBLANK('Appointed Department'[Till]),MAX('Calendar'[Date]),'Appointed Department'[Till])

Step 4: Add a measure

Turnover = SUMX(Turnover,Turnover[Turnover])

The relationships should be as follows...

Please pay attention to the Cardinality and Cross Filter Directions of the relationships

Step 5:

Add a date slicer to your data model using the "Date" field from the "Calendar" table.

Step 6:

Add a matrix visual and add the "Relevant Department" and "Vehicle" Columns from the table named "Vehicle Calendar" into the Rows of the matrix visual.

Step 7:

Add the measure named "Turnover " that we have created in Step 4 to the "Values" section of the matrix visual.

This will give the results as shown in my earlier post.

This may not be the most optimum solution, but once you do this, you will get started and you can further improvise.