Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Tabular Editor - create multiple calculation items

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
)``````

2 REPLIES 2
Super User

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

New Member

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors