Forum Discussion

Ronald123's avatar
Ronald123
Resolver III
2 years ago
Solved

Matrix by year

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 



Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    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

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    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