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

[v-linyulu-msft · Accepted solution]

Thanks for the reply from AntrikshSharma , please allow me to provide another insight:

Hi, @julpol 

Regarding the issue you raised, my solution is as follows:

1.I offer two solutions, and you can choose based on your needs:

One is to use measures:

% of Total1 = 
VAR denominator=CALCULATE(SUM(Table1[CY Rolling 12M]),ALL('Table1'))
VAR Numerator=SUM('Table1'[CY Rolling 12M])
RETURN Numerator/denominator

% of Total 2 = 
VAR denominator=CALCULATE(SUM(Table1[CY Rolling 12M]),ALL('Table1'))

RETURN 'Table1'[CY Rolling 12M]/denominator

2.Here is the final result, which I hope meets your expectations: 

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.