Matrix by year

[Ronald123]

Hello,

I'd like to create a matrix for car expenses and visualize it by year on the X-axis. This should be based on a calculation using the [Date purchase] from the DIM_CARS table.

I've attached a sample file along with the expected outcome.

Could someone please assist me in setting up this matrix in Power BI?

Thank you in advance for your help!

https://drive.google.com/file/d/1SKa9fK--bUFhsFPA0tqQhNKVi_R1tJS6/view?usp=sharing 
https://drive.google.com/file/d/1bLG8F_ntbHAIRVWvy0_6T3IzciUGAtN7/view?usp=sharing 

[MFelix · Accepted solution]

Hi @Ronald123 ,

Try the following:

• Create a parameter table with the years in my case I did it 1 to 20
• Now add the following measure:

Total Costs car =
VAR Depreciation =
    FILTER (
        SUMMARIZE (
            'FACT_DEPRECIATION FINANCIAL',
            DIM_CARS[Registration],
            DIM_CARS[Date purchase],
            'FACT_DEPRECIATION FINANCIAL'[DATE],
            'FACT_DEPRECIATION FINANCIAL'[Amount],
            "_Years", DATEDIFF ( DIM_CARS[Date purchase], 'FACT_DEPRECIATION FINANCIAL'[DATE], YEAR )
        ),
        [_Years] = [Years Value]
    )
VAR maintenance =
    FILTER (
        SUMMARIZE (
            FACT_MAINTAINCE,
            DIM_CARS[Registration],
            DIM_CARS[Date purchase],
            FACT_MAINTAINCE[DATE],
            FACT_MAINTAINCE[Amount],
            "_Years", DATEDIFF ( DIM_CARS[Date purchase], FACT_MAINTAINCE[DATE], YEAR )
        ),
        [_Years] = [Years Value]
    )
RETURN
    SUMX ( Depreciation, 'FACT_DEPRECIATION FINANCIAL'[Amount] )
        + SUMX ( maintenance, FACT_MAINTAINCE[Amount] )

• Format Matrix:
  • Rows: Date Purchase / Registration
  • Columns: Years from the new table
  • Values: Total Costs Car