Tabular Editor - create multiple calculation items

[julpol]

Hi Guys, i'm trying to understand if there is way to use the power of calculation items (tabular editor) to achieve the following result:

i have a table "Table1" with columns : state, date, amount

i have created new measure called Amount Measure in the "Table1" which SUMs the amount

i have created DateTable and linked it with the date field in the "Table1"

I would like to achieve a matrix table having:

- States as rows,

- First two columns would by CY Rolling 12M and PY Rolling 12M (cy for current year and py for previous year).

- Next column would be % of Total (calculated as cy rolling 12m divided by the total of this column)

I have managed to create the CY and PY Rolling 12M calc. items as per below.

But I'm struggling to understand what to do next to create the next % of Total column.

Could you please help?

CY Rolling 12M

VAR CurrentDate = TODAY()  -- Gets the current date
VAR LastDayPreviousMonth = EOMONTH(CurrentDate, -1)  -- Last day of the previous month
VAR Rolling12MStartDate = EDATE(LastDayPreviousMonth, -12)  -- Start date of the rolling 12 months

RETURN
    CALCULATE(
        [Amount Measure],
        DATESBETWEEN(
            'DateTable'[Date],
            EDATE(Rolling12MStartDate, -12)-30,  -- Start date of the rolling 12 months
            Rolling12MStartDate  -- End date of the rolling 12 months
        )
        /*,
        ALL('DateTable'[Year-Month])  -- Ensures that all states are shown
        DATESBETWEEN(
            'DateTable'[Date],
            DATE(2022, 8, 1),  -- Static start date
            DATE(2023, 8, 1)  -- Static end date
        )   
        */
    )



PY Rolling 12M

VAR CurrentDate = TODAY()  -- Gets the current date
VAR Rolling12MStartDate = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)  -- First day of the selected month
VAR Rolling12MEndDate = EOMONTH(Rolling12MStartDate, -1)  -- Last day of the month before the selected month

RETURN
    CALCULATE(
        [Amount Measure],  -- Your measure for GWP
        DATESBETWEEN(
            'DateTable'[Date],
            EDATE(Rolling12MStartDate, -12),  -- Start date of the rolling 12 months
            Rolling12MEndDate  -- End date of the rolling 12 months
        ),
        ALL('DateTable'[Year-Month])  -- Ensures that all states are shown
    )

[AntrikshSharma]

@julpol % of total based on [Amount Measure] or [PY Rolling 12M]/[CY Rolling 12M] ?

[julpol]

Hi @AntrikshSharma 

the formula is :

% of Total = [CY Rolling 12M] / Total of [CY Rolling 12M]

where [CY Rolling 12M] is calc item an currently the last column

Thanks