topic Re: Create calculated column from measure or YTD from measure in DAX Commands and Tips

Create calculated column from measure or YTD from measure

Anonymous — Fri, 26 May 2023 11:06:38 GMT

Hi,

I have the following issue:

I want to calculate the 5m average profit (- 2 months from the current month,current month, + 2months).

I've managed to calculate that with a measure (I'm new to DAX, so probably not the prettiest calculation).

AvProfit5m =

VAR NumMonthsPre = 3

VAR NumMonthsPost = 2

VAR RollingSumPre =

CALCULATE(

SUM('P&L all (2)'[Profit]),

DATESINPERIOD('Calendar'[Date],EOMONTH(MAX('Calendar'[Date]),0),-NumMonthsPre,MONTH)

)

VAR RollingSumPost =

CALCULATE(

SUM('P&L all (2)'[Profit]),

DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),NumMonthsPost,MONTH)

)

RETURN

{RollingSumPost+RollingSumPre}/5

However, when I want to use YTD on top of this measure, it doesn't work and doesn't accumulate the values.

I assume I would have to calculate the 5m average in a calculate column ?

When I use the same calculation to add a new column, it doesn't work. Why?

Is there a way to get the correct results in a column from which I can create a YTD measure or another way to write a measure on top of the existing one that will accumulate my values?

Thanks,

Deborah

Re: Create calculated column from measure or YTD from measure

lbendlin — Sun, 28 May 2023 19:29:46 GMT

Create calculated column from measure

You cannot create a calculated column or calculated table from a measure.

Re: Create calculated column from measure or YTD from measure

Anonymous — Tue, 30 May 2023 14:37:52 GMT

Ok, how do I create an aggregated value / YTD from a measure? 😉

Re: Create calculated column from measure or YTD from measure

lbendlin — Tue, 30 May 2023 15:08:52 GMT

You don't. Create a new measure that implements your desired business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Create calculated column from measure or YTD from measure

Anonymous — Tue, 30 May 2023 16:03:37 GMT

Date		Profit
01.01.2019		2
01.02.2019		3
01.03.2019		4
01.04.2019		5
01.05.2019		6
01.06.2019		9
01.07.2019		10

My actual table has data covering several years.

What I want is to calculate the rolling average for 5 months of the profit (-2 months, current month, +2months) and from there I would like to calculate an aggregate value.

i.e.

Date	Rolling Average	aggregate Rolling Av
01.01.2019
01.02.2019
01.03.2019	4	4
01.04.2019	5,4	9,4
01.05.2019	6,8	16,2
01.06.2019	6	22,2
01.07.2019	5	27,2

I manage to calculate the rolling average with the formula above. But I haven't managed to calculate the **bleep**. rolling average by re-writing the measure. How do I write a measure that calculates the aggregated rolling average? I also have several years in the model, so i need to consider differentiating by the year as well.

Thanks!

Re: Create calculated column from measure or YTD from measure

lbendlin — Thu, 01 Jun 2023 02:12:50 GMT

Rolling Avg 5 = AVERAGEX( WINDOW( -2, REL, 2, REL, SUMMARIZE( ALLSELECTED( 'Table' ), 'Table'[Date],"sm",sum('Table'[Profit] )), ORDERBY( [Date] ) ), [sm] )

cumul RA = VAR maxDate = MAX( 'Table'[Date] ) VAR b = SUMMARIZE( ALLSELECTED( 'Table' ), 'Table'[Date], "ra", [Rolling Avg 5] ) RETURN SUMX( FILTER( b, [Date] <= maxDate ), [ra] )

see attached

Re: Create calculated column from measure or YTD from measure

devanshi — Thu, 01 Jun 2023 06:12:59 GMT

average profit of -2 =

VAR maxdate = MAX([date])

AVERAGEX(DATESINPERIOD([date],maxdate,-2,MONTH),CALCULATE(SUM([profit])))

average profit of +2 =

VAR maxdate = MAX([date])

AVERAGEX(DATESINPERIOD([date],maxdate,2,MONTH),CALCULATE(SUM([profit])))

average profit of 5m = CALCULATE(SUM([profit]),[average profit of -2] ,SELECTEDVALUE([date]),[average profit of +2])

Re: Create calculated column from measure or YTD from measure

Anonymous — Fri, 02 Jun 2023 15:36:56 GMT

Amazing!! Thank you so much!

I have two more questions 😉

a) for my understanding - what do you define with the "sm" and "ra" ? since these are not existing columns..

b) is there a way to only aggregate within the year (Jan - Dec) then start again in January the next year..

Thank you!!!

Re: Create calculated column from measure or YTD from measure

lbendlin — Fri, 02 Jun 2023 15:44:52 GMT

a) those are randomly chosen names for columns in the virtual table created by SUMMARIZE. Feel free to replace with better names.

b) Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Create calculated column from measure or YTD from measure

Anonymous — Tue, 06 Jun 2023 16:14:15 GMT

Thanks so much! I'm still trying to understand the DAX logic.

Here's some sample data and the results I'm trying to achieve. I want each year to be separated in their cumulative results.

Date	Profit	Av 5 Months Profit	Agg. 5m Av Profit
01.01.2019	2
01.02.2019	3
01.03.2019	4	4,00	4,00
01.04.2019	5	5,40	9,40
01.05.2019	6	6,80	16,20
01.06.2019	9	8,40	24,60
01.07.2019	10	8,20	32,80
01.08.2019	12	11,60	44,40
01.09.2019	4	10,80	55,20
01.10.2019	23	9,60	64,80
01.11.2019	5	8,40	73,20
01.12.2019	4	8,20	81,40
01.01.2020	6	4,80	4,80
01.02.2020	3	4,40	9,20
01.03.2020	6	4,80	14,00
01.04.2020	3	15,00	29,00
01.05.2020	6	23,60	52,60
01.06.2020	57	23,00	75,60
01.07.2020	46	23,80	99,40
01.08.2020	3	23,20	122,60
01.09.2020	7	13,00	135,60
01.10.2020	3	4,60	140,20
01.11.2020	6	4,80	145,00
01.12.2020	4	4,60	149,60
01.01.2021	4	4,60	4,60
01.02.2021	6	4,60	9,20
01.03.2021	3	15,20	24,40
01.04.2021	6	23,60	48,00
01.05.2021	57	23,40	71,40
01.06.2021	46	24,00	95,40
01.07.2021	5	28,50	123,90
01.08.2021	6	19,00	142,90

Re: Create calculated column from measure or YTD from measure

lbendlin — Tue, 06 Jun 2023 16:24:39 GMT

cumul RA = VAR maxDate = MAX( 'Table'[Date] ) VAR b = CALCULATETABLE(SUMMARIZE(ALLSELECTED('Table'), 'Table'[Date],"ra",[Rolling Avg 5]),YEAR('Table'[Date])=YEAR(maxDate)) RETURN SUMX(filter(b,[Date]<=maxDate), [ra] )

If you insist on having five values to average from then you need to modify the other measure.