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 attac...
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

     

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