Create calculated column from measure or YTD from measure

[Debooo1]

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

[devanshi]

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

[Debooo1]

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

[lbendlin]

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-...
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/1447...

[lbendlin]

Create calculated column from measure

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

[Debooo1]

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!

[lbendlin]

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

[Debooo1]

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!!! 

[lbendlin]

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.

[Debooo1]

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

[lbendlin]

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.