topic Re: Tabular Editor - create multiple calculation items in DAX Commands and Tips

Tabular Editor - create multiple calculation items

julpol — Thu, 05 Sep 2024 12:31:07 GMT

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 )

Re: Tabular Editor - create multiple calculation items

AntrikshSharma — Thu, 05 Sep 2024 13:05:35 GMT

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

Re: Tabular Editor - create multiple calculation items

julpol — Thu, 05 Sep 2024 20:19:40 GMT

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

Re: Tabular Editor - create multiple calculation items

Anonymous — Sun, 15 Sep 2024 09:18:48 GMT

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.